Stumped by Sum and Countifs Formula

[NorthbyNorthwest]

Hi, everyone. I have a formula that provides me with a count from a large table based on two criteria: review type or status and audit month. The formula returns the correct answer. The formula is:

=SUM(COUNTIFS(tblDivision[Review Type or Status],{"Routine","Makeup","Double"},tblDivision[Audit Month],"January"))


I have a second formula like the one above with a third criteria. The formula returns an incorrect count. It's not remotely close. I do not understand why it doesn't work. The formula is:

=SUM(COUNTIFS(tblDivision[Review Type or Status],{"Routine","Makeup","Double"}, tblDivision[Audit Month],"January",tblDivision[Audit Group],{"Team 1","Team 2"}))

Can someone assist.

 

[Peter_SSs]

Unfortunately it isn't as simple as that when you have multiple columns with multiple choices.
There may be shorter ways but here are a couple of formulas you could try.

=LET(rts,tblDivision[Review Type or Status],am,tblDivision[Audit Month],ag,tblDivision[Audit Group],IFNA(ROWS(FILTER(tblDivision,((rts="Routine")+(rts="Makeup")+(rts="Double"))*(am="January")*((ag="Team 1")+(ag="Team 2")),NA())),0))

=SUMPRODUCT(ISNUMBER(SEARCH("|"&tblDivision[Review Type or Status]&"|","|Routine|Makeup|Double|"))*(tblDivision[Audit Month]="January")*ISNUMBER(SEARCH("|"&tblDivision[Audit Group]&"|","|Team 1|Team 2|")))

 

[StephenCrump]

Further to Peter's comments, your formula as written is counting only three possibilities:

	Review type	Month	Group
1	Routine	January	Team 1
2	Makeup	January	Team 2
3	Double	January	#N/A

Other formulae you could use:

=SUM(BYROW(tblDivision[Review Type or Status]={"Routine","Make up","Double"},LAMBDA(r,SUM(--r)))*(tblDivision[Audit Month]="January")*BYROW(tblDivision[Audit Group]={"Team 1","Team 2"},LAMBDA(r,SUM(--r))))
or
=SUM(MMULT(--(tblDivision[Review Type or Status]={"Routine","Make up","Double"}),{1;1;1})*(tblDivision[Audit Month]="January")*MMULT(--(tblDivision[Audit Group]={"Team 1","Team 2"}),{1;1}))

 

[NorthbyNorthwest]

Unfortunately it isn't as simple as that when you have multiple columns with multiple choices.
There may be shorter ways but here are a couple of formulas you could try.

=LET(rts,tblDivision[Review Type or Status],am,tblDivision[Audit Month],ag,tblDivision[Audit Group],IFNA(ROWS(FILTER(tblDivision,((rts="Routine")+(rts="Makeup")+(rts="Double"))*(am="January")*((ag="Team 1")+(ag="Team 2")),NA())),0))

=SUMPRODUCT(ISNUMBER(SEARCH("|"&tblDivision[Review Type or Status]&"|","|Routine|Makeup|Double|"))*(tblDivision[Audit Month]="January")*ISNUMBER(SEARCH("|"&tblDivision[Audit Group]&"|","|Team 1|Team 2|")))

Hi, Peter. Thanks so much for introducing me to the LET function. The formula works perfectly.

 

[NorthbyNorthwest]

Further to Peter's comments, your formula as written is counting only three possibilities:

	Review type	Month	Group
1	Routine	January	Team 1
2	Makeup	January	Team 2
3	Double	January	#N/A

Other formulae you could use:

=SUM(BYROW(tblDivision[Review Type or Status]={"Routine","Make up","Double"},LAMBDA(r,SUM(--r)))*(tblDivision[Audit Month]="January")*BYROW(tblDivision[Audit Group]={"Team 1","Team 2"},LAMBDA(r,SUM(--r))))
or
=SUM(MMULT(--(tblDivision[Review Type or Status]={"Routine","Make up","Double"}),{1;1;1})*(tblDivision[Audit Month]="January")*MMULT(--(tblDivision[Audit Group]={"Team 1","Team 2"}),{1;1}))

Hi, Stephen. Thanks for the assist. As I wrote Peter, I'm pretty lost when it comes to the newer Excel functions, like LET and LAMDA. I don't want to think about how many hours I would've spent on this had you and Peter not come to my rescue.

 

[Peter_SSs]

Hi, Peter. Thanks so much for introducing me to the LET function. The formula works perfectly.

You're welcome. Thanks for the follow-up.