Using SUMPRODUCT instead of COUNTIFS

I needed to introduce an OR-type of statement into my countifs, but I've read that you can't do that, that you need to use SUMPRODUCT instead.

I'm counting injuries per team per type per week. I'm counting injuries, so I'm looking for: How many people on this TEAM had this INJURY TYPE on any of these 4 DATES.

I've attached a manually entered .xls to show my expected outcome.

In my work document, I have multiple tabs (Week 1, Week 2, etc.) each of which have a manually entered date range. I'd like to be able to copy this array of formulas into each tab so I don't have to adjust the formulas each time. They'll just adjust with the manually entered date ranges.

You can add a helper column in D3:

=A3&"_"&B3&"_"&C3

copied down, then in H4:

=COUNTIF($D$3:$D$20,$G4&"_"&H$3&"_"&$F$3)+COUNTIF($D$3:$D$20,$G4&"_"&H$3&"_"&$F$4)+COUNTIF($D$3:$D$20,$G4&"_"&H$3&"_"&$F$5)

copied down and across...

if you had XL2007 or later, H4 would be:

=SUMPRODUCT(COUNTIFS($A$3:$A$20,$G4,$B$3:$B$20,H$3,$C$3:$C$20,$F$3:$F$5))

copied down and across...

COUNTIFS still works here, it's just that it will return an array of values, i.e. this formula

=COUNTIFS($A$3:$A$20,$G4,$B$3:$B$20,H$3,$C$3:$C$20,$F$3:$F$5)

returns an array like this

{1;0;1}

so you need a SUM or SUMPRODUCT wrapped around that to sum those values and give you the total = 2, so either

=SUM(COUNTIFS($A$3:$A$20,$G4,$B$3:$B$20,H$3,$C$3:$C$20,$F$3:$F$5))

which needs "array entry" with CTRL+SHIFT+ENTER

or you can use SUMPRODUCT in place of SUM - it's only summing here but has the advantage that it doesn't require "array entry"

This sort of approach is more efficient over large ranges because COUNTIFS is doing most of the work and that's a more efficient function than SUMPRODUCT

See above few posts...

Originally Posted by SymphonyTomorrow

And 55 minutes later, it is done calculating across about 900 fields

How much data do you have? How many dates are you using in the "OR" part? If those dates are consecutive you could just add two conditions in COUNTIFS - one for >= the first date and one for <= the last date.....