We all know that SUMIF formula can be used to find the sum of values meeting a criteria. Like this,
But I was pleasantly surprised to realize that SUMIF works equally well for 2D ranges too, like this:
During a recent consulting work with a client I had a requirement to sum up values that meet some criteria across columns and I wasn’t sure if the SUMIF would hold. Boy, I was wrong. It worked nicely and I still stand by my statement “If I get a dollar for every COUNTIF / SUMIF I write, I will have million dollar bills.“.
Did you try 2D SUMIF?
Share your tips and ideas thru comments.
Bonus: This should work for COUNTIF, SUMIFS, COUNTIFS etc too. But you already knew it.
11 Responses to “Who is the most consistent seller? [BYOD]”
The Date column in the sample file is Text not Dates
[…] http://chandoo.org/wp/2015/02/18/calculating-consistency-in-excel/?utm_source=feedburner&utm_med… […]
Great Chandoo. Keep it up, Looking forward more from BYOD..
Thanks
With Excel 2013 the pivot table could be connected to the data model which provides a distinct count.
This will do for invoice count
=COUNTIF(F:F,H12)
Instead of
=COUNTIFS(sales[SELLER],$H12)
Excellent document. How did you make the last graphic? Witch app. Thanks for answer.
Can someone tell me what =countif(sales[date],sales[date]) is counting? The value is 19. Its found in the =SUMPRODUCT(IF(sales[SELLER]=H12,1/COUNTIFS(sales[SELLER],H12,sales[date],sales[date]),0))
Hi Chris,
=countif(sales [date],sales[date]) function is counting the unique dates in the table.
Vândalo
Excellent document!
Can you explain more about the calculation on Weighted consistency? More specific the small number is 0,00001 ?
How come the number should be smaller if there is more sellers?
Hi,
Not understood this formula: {=SUMPRODUCT(IF(sales[SELLER]=H12,1/COUNTIFS(sales[SELLER],H12,sales[date],sales[date]),0))}
Please explain.
Thanks.