*HELP PLEASE* - Row count with two criterias...

Allison C

I am trying to build two summaries in one of my sheets and finding the formula for this has defeated me. :(

I need to know how many accounts in the Deal Yype column have "SC+" in the description, but I need a count for 2022 & 2023. So, how many SC+ account went live in 2022 and how many went live in 2023.

This is one of the many formulas I have tried...without success.

=COUNTIFS([Deal Type]:[Deal Type], CONTAINS("to SC+", @cell), [Go Live Date]:[Go Live Date], ISDATE("2023", @cell))

Any help would be GREATLY appreciated!

Kelly Moore

Allison

The formula should be

=COUNTIFS([Deal Type]:[Deal Type], CONTAINS("to SC+", @cell), [Go Live Date]:[Go Live Date], YEAR(@cell)=2023)

the missing ending parenthesis could give you the error

Kelly Moore

Hey @Allison C

Just a couple of small tweaks

The ISDATE() is looking to see if the date is a date, vs say a textstring. To limit to a Year, use the YEAR function

=COUNTIFS([Deal Type]:[Deal Type], CONTAINS("to SC+", @cell), [Go Live Date]:[Go Live Date], YEAR(@cell)=2023

Note that numbers are not enclosed in quotes unless you want them to behave as a text string.

Does this work for you

Kelly

Allison C

Kelly,

Thank you again for help with this!

I copied and pasted your formula and it worked perfectly, but then when I closed and opened my sheet up I am getting an "INVALID DATA TYPE". Nothing has changed in the cells, I recopied and pasted your formula (which makes perfect sense) and now it won't work.

Any idea on what could have changed?

Kelly Moore

Hey @Allison C

I see I somehow lost my closing parenthesis for the formula. Did you add it back?

Kelly Moore

Allison

The formula should be

=COUNTIFS([Deal Type]:[Deal Type], CONTAINS("to SC+", @cell), [Go Live Date]:[Go Live Date], YEAR(@cell)=2023)

the missing ending parenthesis could give you the error

Allison C

Thank you, that worked perfectly!