COUNTIFS and SUMIFS with multiple variable choices

Hey guys and gals.

I'm trying to write some COUNTIFS and SUMIFS functions with multiple variables choices, but my formulas don't seem to work like I intend them to. And I'm not sure why. Here's an example of what I have:

Please Login or Register  to view this content.

The formula "should" return the number of lines that have either "AF" or "A" in 6 different columns of a table called TRACKDIND. However, it doesn't seem to work properly, as soon as I add the second {"AF","A"} criteria.

Am I missing something ?

It looks like the formula is counting the number of rows where the selected columns are all "AF" and then adding the number of rows where the selected columns are all "A" instead of counting the number of rows where the selected columns are either "AF" or "A".

You could use this:

=COUNTIFS(TRACKDIND[ACHIND],"A*",TRACKDIND[LUNIND],"A*",TRACKDIND[MARIND],"A*",TRACKDIND[MERIND],"A*",TRACKDIND[JEUIND],"A*",TRACKDIND[VENIND],"A*")

I appreciate the rep!

Honestly, I'm stumped as to why the SUM COUNTIFS formula is acting the way it is.

SUMPRODUCT will work and you used it correctly. It is just unfortunate that the shorter formula isn't working as we expect it to.

I'm going to reach out to the community to see if anyone can answer why this isn't working as we expect it to.

Posting a small representative sample here for others to test on.

To the community, request is to count the number of rows that are all green (A or AF).

That array version will only work with two arrays at most, and you have to make sure that one array is orientated oppositely from the other in order to get effectively a cartesian product of outcomes. If they're aligned the same way as here (which is unavoidable with so many), you effectively just get two COUNTIFS added together. In other words, using {"A", "AF"} and {"A"; "AF"} would give you all 4 possible permutations (first array is 'horizontal', second is 'vertical'), whereas {"A", "AF"} and {"A", "AF"} only gives you two combinations ("A" and "A", and "AF" and "AF").

=SUMPRODUCT(--(MMULT(--((TRACKDIND="AF")+(TRACKDIND="A")),{1;1;1;1;1;1})=6))

seems to work with 63FD's sample...

Yes. see file. There is a "clever" way to populate the series of 1;1;1;1 I just can't recall it right now.

This works... but isn't the way I had in mind...


array entered.

Yes!!!!! I remembered.

No need to array enter this one.

=SUMPRODUCT(--(MMULT(--((TRACKDIND="AF")+(TRACKDIND="A")),ROW(INDIRECT("1:"&COUNTA(A1:J1)))-ROW(INDIRECT("1:"&COUNTA(A1:J1)))+1)=6))

OK, KomicJ.... Finally here it is, on your own sheet.

=SUMPRODUCT(--(MMULT(--((TRACKDIND[[ACTIND]:[VENIND]]="AF")+(TRACKDIND[[ACTIND]:[VENIND]]="A")),ROW(INDIRECT("1:"&COUNTA(TRACKDIND[[#Headers],[ACTIND]:[VENIND]])))-ROW(INDIRECT("1:"&COUNTA(TRACKDIND[[#Headers],[ACTIND]:[VENIND]])))+1)=6))

Originally Posted by Glenn Kennedy

Yes. see file. There is a "clever" way to populate the series of 1;1;1;1 I just can't recall it right now.

Glenn FWIW here's another way.

Replace ROW(INDIRECT("1:"&COUNTA(A1:J1)))-ROW(INDIRECT("1:"&COUNTA(A1:J1)))+1

With ROW(INDIRECT("1:"&COUNTA(A1:J1)))^0

Dave. The Force is with you!!