COUNTIF with Multiplicities

Question

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.

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

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:

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.

Answer 1

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)))