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:

Date	Shirt	Sales New	Sales Change (from last year)
Monday	Blue shirt	22	+2
Monday	White shirt	25	-5
Tuesday	Blue shirt	#N/A	#N/A
Wednesday	Blue shirt	15	+5
Wednesday	Red shirt	20	0

<tbody>
</tbody>

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.