Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct Help
I currently use the formula below that works fine however I need to add in
another condition that only returns a value if the department number is in the range of say 1400 to 1499 =SUMPRODUCT(--('[2010-01 Group Trial Balance.xls]Group Trial Balance - Current'!$B$4:$B$278=$A68)*('[2010-01 Group Trial Balance.xls]Group Trial Balance - Current'!$D$4:$BY$278)) Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct Help
Adding book reference/sheet references back in yourself, the syntax would be:
=SUMPRODUCT(--($B$4:$B$278=$A68), --($C$4:$C$278=1400), --($C$4:$C$278<1500), $D$4:$BY$278)) -- "Actually, I *am* a rocket scientist." -- JB (www.MadRocketScientist.com) Your feedback is appreciated, click YES if this post helped you. "Curtis" wrote: I currently use the formula below that works fine however I need to add in another condition that only returns a value if the department number is in the range of say 1400 to 1499 =SUMPRODUCT(--('[2010-01 Group Trial Balance.xls]Group Trial Balance - Current'!$B$4:$B$278=$A68)*('[2010-01 Group Trial Balance.xls]Group Trial Balance - Current'!$D$4:$BY$278)) Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct Help
I just noticed the final range wasn't a single column, so you might need this:
=SUMPRODUCT(($B$4:$B$278=$A68) * ($C$4:$C$278=1400) * ($C$4:$C$278<1500), $D$4:$BY$278)) -- "Actually, I *am* a rocket scientist." -- JB (www.MadRocketScientist.com) Your feedback is appreciated, click YES if this post helped you. "JBeaucaire" wrote: Adding book reference/sheet references back in yourself, the syntax would be: =SUMPRODUCT(--($B$4:$B$278=$A68), --($C$4:$C$278=1400), --($C$4:$C$278<1500), $D$4:$BY$278)) -- "Actually, I *am* a rocket scientist." -- JB (www.MadRocketScientist.com) Your feedback is appreciated, click YES if this post helped you. "Curtis" wrote: I currently use the formula below that works fine however I need to add in another condition that only returns a value if the department number is in the range of say 1400 to 1499 =SUMPRODUCT(--('[2010-01 Group Trial Balance.xls]Group Trial Balance - Current'!$B$4:$B$278=$A68)*('[2010-01 Group Trial Balance.xls]Group Trial Balance - Current'!$D$4:$BY$278)) Thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct Help
Thx
"JBeaucaire" wrote: I just noticed the final range wasn't a single column, so you might need this: =SUMPRODUCT(($B$4:$B$278=$A68) * ($C$4:$C$278=1400) * ($C$4:$C$278<1500), $D$4:$BY$278)) -- "Actually, I *am* a rocket scientist." -- JB (www.MadRocketScientist.com) Your feedback is appreciated, click YES if this post helped you. "JBeaucaire" wrote: Adding book reference/sheet references back in yourself, the syntax would be: =SUMPRODUCT(--($B$4:$B$278=$A68), --($C$4:$C$278=1400), --($C$4:$C$278<1500), $D$4:$BY$278)) -- "Actually, I *am* a rocket scientist." -- JB (www.MadRocketScientist.com) Your feedback is appreciated, click YES if this post helped you. "Curtis" wrote: I currently use the formula below that works fine however I need to add in another condition that only returns a value if the department number is in the range of say 1400 to 1499 =SUMPRODUCT(--('[2010-01 Group Trial Balance.xls]Group Trial Balance - Current'!$B$4:$B$278=$A68)*('[2010-01 Group Trial Balance.xls]Group Trial Balance - Current'!$D$4:$BY$278)) Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct with Condition OR Sumproduct with ADDRESS function - HE | Excel Discussion (Misc queries) | |||
Conditional SUMPRODUCT or SUMPRODUCT with Filters | Excel Worksheet Functions | |||
sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
Sumproduct | Excel Worksheet Functions | |||
SUMPRODUCT - Help | Excel Worksheet Functions |