# COUNTIF with Multiplicities

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.

• @marikamitsos Will do, not at computer now. Will check out it works when back. Thanks for response & comment
– SDH
Dec 21 '19 at 2:30

``````=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)))
`````` • Please remember that as per site guidelines when an answer addresses your question, accept it and even upvote it so others can benefit as well. May 19 '20 at 11:15