Excel countif and sumif together

Question

I am trying to write a formula in Excel which will count how many times we have sold less than 50 of a particular product. For example, here is a day's sales:

Order | Product | Qty
1     |    A    |  5 
2     |    A    |  5
3     |    A    |  5
4     |    B    | 30
5     |    C    | 75

I want a formula in a cell which says how many times we have a requirement for less than 50 of a certain product. So in the example above, there is a total of 15 As, 30 Bs and 75 Cs, so 2 of those are less than 50.

I think it will need to be an array function of COUNTIF and SUM, but can't figure it out.

Answer 1

You could use this formula:

=SUMPRODUCT(--(IF(ROW($B$2:$B$10)=MATCH($B$2:$B$10,$B$1:$B$10,0),SUMIF($B$2:$B$10,$B$2:$B$10,$C$2:$C$10),"")<50))

Note: It's an array formula and must be entered through Ctrl+Shift+Enter

Product order placement can be randomized and does not have to be in order.

Answer 2

Another way

=SUMPRODUCT((SUMIF(B2:B10,B2:B10,C2:C10)<50)/COUNTIF(B2:B10,B2:B10))

Answer 3

Maybe something like that will help:

=SUMPRODUCT(--IF($B$2:$B$11<>$B$1:$B$10,SUMIF($B$2:$B$11,$B$2:$B$11,$C$2:$C$11)<50,0))

Note that this is an array formula so needs to be entered with Ctrl+Shift+Enter. Data needs to be sorted by Product (i.e. product A cannot appear in random rows, like row 2, 20 and 100; it needs to be grouped together).

Result: