Forum Discussion
SUMIF using non-contiguous cells
I am looking for help in how to structure a formula to sum the amounts based on expiration dates. I am stumped on how to use the SUMIF function for what I am trying to do or if that is even the correct function to use.
I am trying to add the total quantities based on if they are expired or not. The expiration dates are based off using the TODAY function. Is it possible to do this since these are in non-contiguous cells?
As an example: how do I get the total expired of Product A in J4 and total not expired in K4?
Any help is appreciated.
I am using Excel 365, Windows 10, laptop
=SUMPRODUCT(((C4<TODAY())*B4)+((E4<TODAY())*D4)+((G4<TODAY())*F4))
You could use sumproduct function.
3 Replies
=SUMPRODUCT(((C4<TODAY())*B4)+((E4<TODAY())*D4)+((G4<TODAY())*F4))
You could use sumproduct function.
- Thank you!! That worked. I really appreciate your help on this.
After playing around and testing I also was able to figure out that the following works as well:
=SUM(IF(C5<TODAY(),B5)+IF(E5<TODAY(),D5)+IF(G5<TODAY(),F5))
Thank you so much again for all your help.
or if that is even the correct function to use.
The real question should be: Is this the right setup for a SUMIF() formula?
And the answer would be: No.
You always want to use a list of records:
Product - Quantity - Expire date
Then a column "Expired?" with the formula you used in the conditional formatting.
Then you could do the SUMIF() or even a pivot table.
