Nested subtraction in SUMIFS

[filido]

Hi,

I'm trying to populate a formula that sums all the sales of a particular item with few conditions:

=SUMIFS(Table1[Sales New] - Table1[Sales Change]; Table1[Sales New] - Table1[Sales Change];">0";Table1[Sales New];">0";Table1[Item];"Blue shirt")

--> Table1[Sales New]-Table1[Sales Change] = Old Sales of the blue shirt
--> Table1[Sales New] = New Sales of the blue shirt
--> Table1[Item];"Blue shirt" = Find item "Blue shirt" from list of items

So this is what I want: SUM all blue shirts' Old Sales, IF Old Sales AND New Sales are both over 0.

Excel however says that there's an error with this formula. Any thoughts?

I know I could get around this by creating a column for Old Sales but for the sake of the file size, I want to keep the number of columns as minimum as possible.

I also tried this: Table1[Price Old] * Table1[Quantity] to get the old sales but this also caused an error.

=SUMIFS(Table1[Price Old] * Table1[Quantity]; Table1[Price Old] * Table1[Quantity];">0";Table1[Sales New];">0";Table1[Item];"Blue shirt")

Thanks in advance!

 

[steve the fish]

Sumifs only accepts ranges for its sum ranges and criteria ranges. You would have to use sumproduct to do subtraction.

=SUMPRODUCT((Table1[Sales New] - Table1[Sales Change]),--((Table1[Sales New] - Table1[Sales Change])>0),--(Table1[Sales New]>0),--(Table1[Item]="Blue shirt"))

 

[filido]

Hi,

That formula returns #N/A. Is the formula affected if e.g. some sales of the blue shirt is #N/A? Below is an example data:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Shirt[/TD]
[TD]Sales New[/TD]
[TD]Sales Change (from last year)[/TD]
[/TR]
[TR]
[TD]Monday[/TD]
[TD]Blue shirt[/TD]
[TD]22[/TD]
[TD]+2[/TD]
[/TR]
[TR]
[TD]Monday[/TD]
[TD]White shirt[/TD]
[TD]25[/TD]
[TD]-5[/TD]
[/TR]
[TR]
[TD]Tuesday[/TD]
[TD]Blue shirt[/TD]
[TD]#N/A[/TD]
[TD]#N/A[/TD]
[/TR]
[TR]
[TD]Wednesday[/TD]
[TD]Blue shirt[/TD]
[TD]15[/TD]
[TD]+5[/TD]
[/TR]
[TR]
[TD]Wednesday[/TD]
[TD]Red shirt[/TD]
[TD]20[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

Because Tuesday's blue shirt data is unavailable, is my SUMPRODUCT formula affected by it and causes the formula to return #N/A?

If #N/A doesn't affect the SUMPRODUCT, what else might be the problem?

 

[steve the fish]

Yes you will have to correct the n/a for all entries in the column. Use an iferror formula. You could also do with using the same headers as you suggested in post 1.