Can you use the COUNTIF function with another sum formula?

Mel Plummer

Hi

I was wondering if someone might be able to help me please. I have a sheet that lists PPE items, i.e. Item, size, quantity taken etc. I have used COUNTIF to identity what has been taken using the following:

=COUNTIFS(Item:Item, "hi-viz polo shirt", Quantity:Quantity, >0)

Which returns the number of rows that have the item listed, however I also need it add up the quantities as well. For example, at the moment it shows there are 6 entries in the form which there are but I also need it to add up the quantities as the quantity taken is higher, i.e. six entries but 10 shirts taken.

Is there a way to do this? Any help would be greatly appreciated.

TIA 😊

David Tutwiler

This should do it:

SUMIFS(Quantity:Quantity, Item:Item, 'hi-viz polo shirt", Quantity:Quantity, >0)

Mel Plummer

Hi David

Thank you for your response. That didn't actually work, but you gave me an idea so I tweaked your formula slightly and it returned the correct value. I amended it to the following:

=SUMIFS(Quantity:Quantity, Quantity:Quantity, >0, Item:Item, "Hi-Viz Polo Shirt")

Kind regards

Mel

David Tutwiler

Good deal. It appears the part that didn't work was the capitalization in the Hi-Viz descriptor.

Either way, glad it is working for you now.