Google-sheets – COUNTIF with Multiplicities

google sheetsgoogle-sheets-arrayformula

I am currently creating a histogram on Google Sheets to count how many of x-product in our inventory we have priced at less than $10k, between $10k to $20k, $20k to $30k, etc.

enter image description here

The problem is that some our products have multiplicities greater than 1 —we have two of x-product priced at $50k for example.

enter image description here

Thus the normal COUNTIFS function is not picking up these multiplicities and undercounting them. Here's the COUNTIF function I'm using:

=COUNTIFS(J5:J50,">=10000", J5:J50,"<20000")

I've come across the SUMPRODUCT function, and have read it can work with other functions, so I think a solution may lie here. I'm using:

=SUMPRODUCT(COUNTIFS(J5:J50,">=10000", J5:J50,"<20000"),F5:F50)

However, I'm gettin #VALUE returned with the following message:

enter image description here

Anybody who can provide clarity on what I'm doing wrong or direct me to another function that can solve the problem, it would be greatly, greatly appreciated.

Best Answer

Please try this formula:

=SUM(ArrayFormula(IF(
       (F5:F55>0) * (J5:J55>=50000) * (J5:J55<70000),F5:F55,0)))

Pro tip

Instead of editing the formula every time you want a different range, you can use the following formula and use the values you enter in cells K1 and L1 respectively. 

=SUM(ArrayFormula(IF(
       (F5:F55>0) * (J5:J55>=K2) * (J5:J55<L2),F5:F55,0)))

enter image description here

Related Topic