Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
subtract a single cell from range of cells and then count | Excel Worksheet Functions | |||
trying to subtract a range from a cell | Excel Worksheet Functions | |||
subtract a range from an added range | Excel Discussion (Misc queries) | |||
I need to subtract a range of numbers | Excel Worksheet Functions | |||
Subtract Range Of Numbers using a Formula | Excel Discussion (Misc queries) |