Count Ifs for Multiple IF fields

Drea Mora

Hello,

I'm trying to write a formula that calculates the remaining items in an inventory category and within a certain date range. This count ifs would subtract from a fixed number everytime we restock inventory.

The inventory total on 8/22 for item #2 was 45. My team creates a new row everytime an item is distributed at our clinic and to whom. I want a formula to calculate how many of item #2 we have left in inventory since inventory was last counted on 8/22.

45 - (countif Date of CM is after 8/22/23 and the item number is 2) = ??

Thanks for your help!

Eric Law

@Drea Mora Will you ever restock an item? Is there a form people are submitting from? Why do you need a certain date range, and will the date range shift? Is this a one time pull, or a continual pull?

Drea Mora

@Eric Law Yes we purchase more inventory and manually count what's remaining in stock every quarter so we would have a new total fixed number at a later date.

Eric Law

@Drea Mora So you wouldn't be interested in a rolling inventory count?

Paul Newcome

Try this:

=45 - COUNTIFS([Item Number]:[Item Number], @cell = 2, [Date of CM]:[Date of CM], @cell> DATE(2023, 08, 22))