I want to add or subtract a range of cells
 

I am trying to add a range if another range is "D" or subtract if the "D" is
an "R".

I have used SUMIF to add the range but cannot figure our how to not add
those with criteria of "R" and in fact subtract them with criteria of "R".

Example: =SUMIF('Inventory Sheet'!B$9:'Inventory
Sheet'!B$10000,A9,'Inventory Sheet'!$F$9:'Inventory Sheet'!$F$9999)

Any suggestions would be appreciated.

I want to add or subtract a range of cells
 

With A1 thru B9 containing:

1 d
2 d
3 d
4 d
5 r
6 r
7 r
8 d
9 d


=SUMPRODUCT(--(B1:B10="d"),A1:A10)-SUMPRODUCT(--(B1:B10="r"),A1:A10)

will yield 9
--
Gary''s Student - gsnu201003


"wormburner" wrote:

I am trying to add a range if another range is "D" or subtract if the "D" is
an "R".

I have used SUMIF to add the range but cannot figure our how to not add
those with criteria of "R" and in fact subtract them with criteria of "R".

Example: =SUMIF('Inventory Sheet'!B$9:'Inventory
Sheet'!B$10000,A9,'Inventory Sheet'!$F$9:'Inventory Sheet'!$F$9999)

Any suggestions would be appreciated.

I want to add or subtract a range of cells
 

Maybe this...

=SUM(SUMIF('Inventory Sheet'!B$9:B$10000,{"D","R"},'Inventory
Sheet'!$F$9:$F$9999)*{1,-1})


--
Biff
Microsoft Excel MVP


"wormburner" wrote in message
...
I am trying to add a range if another range is "D" or subtract if the "D"
is
an "R".

I have used SUMIF to add the range but cannot figure our how to not add
those with criteria of "R" and in fact subtract them with criteria of "R".

Example: =SUMIF('Inventory Sheet'!B$9:'Inventory
Sheet'!B$10000,A9,'Inventory Sheet'!$F$9:'Inventory Sheet'!$F$9999)

Any suggestions would be appreciated.