Formula Help

Grace Tidwell

Hello- I am trying to subtract the most recent # of vaccines on hand from the sum of the total vaccines delivered- any suggestions?

Tried this but it did not work: =(SUM([If you got a shipment, how many doses were in it?]2:[If you got a shipment, how many doses were in it?]61)) - (INDEX([# of vaccine doses on hand today (#s only)]2:[# of vaccine doses on hand today (#s only)]61, MATCH(MAX(COLLECT([Today's Date (Fill daily)]2:[Today's Date (Fill daily)]61, [# of vaccine doses on hand today (#s only)]2:[# of vaccine doses on hand today (#s only)]61, @cell <> "")), 0)))

Kelly Moore

Hey @Grace Tidwell

I used your criteria. As written, this takes the sum of all delivered vs only the most current amount on hand. Is this how you wanted the formula written?

=SUMIFS([If you got a shipment, how many doses were in it?]2:[If you got a shipment, how many doses were in it?]61, [If you got a shipment, how many doses were in it?]2:[If you got a shipment, how many doses were in it?]61, <>"") - INDEX((COLLECT([# of vaccine doses on hand today (#s only)]2:[# of vaccine doses on hand today (#s only)]61, [Today's Date (Fill daily)]2:[Today's Date (Fill daily)]61, @cell = MAX([Today's Date (Fill daily)]2:[Today's Date (Fill daily)]61), [Today's Date (Fill daily)]2:[Today's Date (Fill daily)]61, ISDATE(@cell), [# of vaccine doses on hand today (#s only)]2:[# of vaccine doses on hand today (#s only)]61, @cell <> "")), 1)

Let me know if this works and if you need to tweak it further

cheers,

Kelly