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.

  • @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

Please try this formula:

       (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.

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

enter image description here

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.