If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
Thread Tools | |
Display Modes | |
|
|