Prev: Unwanted reformatting of protected cells
Next: How can I put a text box in every cell?
From: wormburner on 31 May 2010 16:07
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.
From: Gary''s Student on 31 May 2010 16:14
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.
From: T. Valko on 31 May 2010 18:03
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" <wormburner(a)discussions.microsoft.com> wrote in message
news:B0620A1C-FD4B-4232-B205-664A46D1C26B(a)microsoft.com...
>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.


 | 
Pages: 1
Prev: Unwanted reformatting of protected cells
Next: How can I put a text box in every cell?