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.
Last edited by SymphonyTomorrow; 11-18-2011 at 03:30 PM.
Use this formula in H4
=SUMPRODUCT(($A$3:$A$20=$G4)*($B$3:$B$20=H$3)*(($C$3:$C$20=$F$3)+($C$3:$C$20=$F$4)+($C$3:$C$20=$F$5) ))
Please amend your title. The strong language is not required - Thx
Quoting entire posts clutters the forum and makes threads hard to read !
If you are pleased with a member's answer then use the Star icon to rate it
Click here to see forum rules
@Whizbang,
Thanks, that worked. Unfortunately the processing requirements are absurd!
I am not sure what you mean.
The following article is great for learning/understanding SUMPRODUCT.
http://xldynamic.com/source/xld.SUMPRODUCT.html
I mean only that deploying this across my whole matrix is taking a very long time to calculate.
Yeah. Unfortunately counting/summing by multiple criteria requires array solutions. Even though SUMPRODUCT is not an array formula in the sense that you confirm with CTRL+SHIFT+ENTER, it still calculates like one (though supposedly it is a little faster than an equivelant array formula).
You can improve performance by using dynamic ranges, to ensure the smallest posible ranges are calculated, but this is offset very quickly when you have dozens or hundreds of SUMPRODUCT calculations. At which point, another solution should probably be found (suchas pivot tables, or queries).
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$2 0,$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...
Last edited by NBVC; 11-18-2011 at 03:53 PM.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
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
Audere est facere
And 55 minutes later, it is done calculating across about 900 fields
See above few posts...
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
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.....Originally Posted by SymphonyTomorrow
Audere est facere
WOW.
What a difference!
daddylonglegs, I used your COUNTIFS modifier across my full array, with the Ctrl+Shift+Enter, and the calculation was done < 60 seconds, compared to 55 min with SUMPRODUCT
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks