A better way to COUNTIFS + COUNTIFS + COUNTIFS

[RoundaboutCJP]

Hi,

I have a lot of formula's that consist of
=(COUNTIFS,range,criteria1,criteria2,criteria3)+(COUNTIFS,range,criteria1,criteria2,criteria3)+(COUNTIFS(range,criteria1,criteria2,criteria3)

In all situations criteria 1 and criteria 2 are the same, the reason for the addition between the formula is that criteria 3 changes in each COUNTIFS within the full formula.

Is there a better way of constructing this formula to strip back the massive number of COUNTIFS that my spreadsheet now has which is slowing it down?

Thanks so much

Chris

[daddylonglegs]

If criteria 3 is a list you can do this

=SUMPRODUCT(COUNTIFS(range1,crit1,range2,crit2,range3,crit3))

[Zer0Cool]

You could do a SUMPRODUCT with OR logic. It would be something like:

Please Login or Register  to view this content.

My guess would be that if a single record has say crit3a, crit3b and crit3c all true that you dont want to count them 3x (as would your original formula), only once? The above will count multiple times. Its most useful when the 3 criteria are in the same column and thus 1 record cant be true for more than 1/3.

If you wanted to adjust it you can do:

Please Login or Register  to view this content.

Essentially for that record if 1 or more are true return true (1), thus counting 1,2 or 3 as 1

Hope this helps

[RoundaboutCJP]

Hi,
I'm not sure if it is a list

My full formula is:

=(COUNTIFS(A:A,E1,B:B,F1,C:C,X1) + (COUNTIFS(A:A,E1,B:B,F1,C:C,Y1) + (COUNTIFS(A:A,E1,B:B,F1,C:C,X1))

It's the 3rd criteria that changes from X to Y and Z in the formula.

Would SUMPRODUCT be able to handle this?

Lying behind this is the fact that X1, Y1 and Z1 are all controllable - so I can change what lies in them to then alter the COUNT criteria.

[xlnitwit]

Yes- as DaddyLongLegs suggested
=SUMPRODUCT(COUNTIFS(A:A,E1,B:B,F1,C:C,X1:Z1))
should suffice.