Sumproduct incorpating two criteria involving same references

Last Edited By Krjb Donovan
Last Updated: Mar 05, 2014 09:57 PM GMT

Question

Just a follow up question from the last solution you posted.


I'm trying to count the number of entires (row of information) that matches the following 3 criteria: 1) Cells A13 to A1500 matches the text in cell o3 2) Cells AI13 to AI1500 matches the"Active" 3) Cells AM13 to AM1500 value is greater than 0.90 and less than 0.95

I've tried the following:

=SUMPRODUCT(--($A$13:$A$1500=O3),--($AI$13:$AI$1500="Active"),--(IF(ISERROR($AM$13:$AM$1500),0,$AM$13:$AM$1500)>0.9),--(IF(ISERROR($AL$13:$AL$1500),0,$AL$13:$AL$1500)<0.95))

But it does work. I don't think I'm treating the two criteria in item 3 correctly.


Answer

the only thing I see wrong is in the check for < 0.95, you are looking at column AL instead of column AM

=SUMPRODUCT(--($A$13:$A$1500=O3),--($AI$13:$AI$1500="Active"),--(IF(ISERROR($AM$13:$AM$1500),0,$AM$13:$AM$1500)>0.9),--(IF(ISERROR($AL$13:$AL$1500),0,$AL$13:$AL$1500)<0.95))

should be

=SUMPRODUCT(--($A$13:$A$1500=O3),--($AI$13:$AI$1500="Active"),--(IF(ISERROR($AM$13:$AM$1500),0,$AM$13:$AM$1500)>0.9),--(IF(ISERROR($AM$13:$AM$1500),0,$AM$13:$AM$1500)<0.95))

And you would need to enter the formula as an array formula with Ctrl+Shift+enter

I put some test data in a sheet and when I used the new formula, entered as an array formula, it gave me the correct answer (As I understand the criteria you set forth).

Advertisement

©2017 eLuminary LLC. All rights reserved.