Since its impossible to use sumif or countif because the criteria is always static and not a range
I was wondering if it can be done in any type of formula.
would look something like this:
A B C D
1
2 Min Good Great Score
3 100 250 500 650
4 100 250 500 200
5 50 150 300 200
6 100 250 500 700
7 25 75 125 150
8 100 250 500 175
9 ________________
10 xx xx xx
2 1 3 <--------what the xx should be.
what xx would be is to countif d3:d8<=a3:a8 and d3:d8<b3:b8
counts how many people got to min.
Looking for how many people got to each range, Min Good Great.
Since all these ranges change, you can’t just put in a number for sumif or count.
Been working on it for awhile and can't figure a way to get it.
John
Hi John,
Please attach a workbook with your data when posting. Saves you and us typing everything in again.
Please see attached file.
I've converted the data to a table for ease of use.
I'm using Office 365 so I can use arrays 'natively'. If you're using a version of Excel that doesn't support the new array functions, you can enter the same formulae using CTRL+SHIFT+ENTER. I've included both ways in the workbook.
As an example of what's in the workbook, the following formula returns 2 arrays of TRUE/FALSE after comparing the scores. Multiplying these together gives an array of 1's and 0's corresponding to the True/False comparisons.
=SUM(IF((Table1[Score]>=Table1[Min])*(Table1[Score]<=Table1[Good]),1,0))
IF then converts that to an array or 1's or 0's that SUM adds up.
Cheers
Phil
That is genius. Thanks so much. An you bet I’ll attach a workbook next time.
Thanks again,
John
