Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Help with Sumproduct with Indirect
Hi
I am currently using the following formula... =SUMPRODUCT((INDIRECT("$E$1:$AN$1")<"Cat")*INDIRE CT("$E$3:$AN$3")) I need to add another criterium "Dog". How do I modify? Thanks Rob |
#2
|
|||
|
|||
One way:
=SUMPRODUCT(--($E$1:$AN$1<"Cat"),--($E$1:$AN$1<"Dog"),$E$3:$AN$3)) convert ranges to INDIRECTs if necessary. In article , Rob wrote: I am currently using the following formula... =SUMPRODUCT((INDIRECT("$E$1:$AN$1")<"Cat")*INDIRE CT("$E$3:$AN$3")) I need to add another criterium "Dog". How do I modify? |
#3
|
|||
|
|||
Hi
Why do you use INDIRECT here? Simply =SUMPRODUCT(($E$1:$AN$1<"Cat")*($E$3:$AN$3)) will work in same way. Even better will be =SUMPRODUCT(--($E$1:$AN$1<"Cat"),($E$3:$AN$3)) About your question - for which range will the additional criterium apply? When you want to sum all values in row 3 with "Dog" or "Cat" in row 1, then =SUMPRODUCT(--($E$1:$AN$1<"Dog"),($E$3:$AN$3))+SUMPRODUCT(--($E$1:$AN$1<"C at"),($E$3:$AN$3)) When you want to sum all values in row 3 with "Cat" in row 1 and "Dog" in row 2, then =SUMPRODUCT(--($E$1:$AN$1<"Cat"),--($E$2:$AN$2<"Dog"),($E$3:$AN$3)) Arvi Laanemets "Rob" wrote in message ... Hi I am currently using the following formula... =SUMPRODUCT((INDIRECT("$E$1:$AN$1")<"Cat")*INDIRE CT("$E$3:$AN$3")) I need to add another criterium "Dog". How do I modify? Thanks Rob |
#4
|
|||
|
|||
The only advantage I can see to INDIRECT is that it allows one to insert
columns/rows without the formula automatically adjusting. I probably could come up with a scenario where that made sense. In article , "Arvi Laanemets" wrote: Why do you use INDIRECT here? |
#5
|
|||
|
|||
Thanks!
"JE McGimpsey" wrote: One way: =SUMPRODUCT(--($E$1:$AN$1<"Cat"),--($E$1:$AN$1<"Dog"),$E$3:$AN$3)) convert ranges to INDIRECTs if necessary. In article , Rob wrote: I am currently using the following formula... =SUMPRODUCT((INDIRECT("$E$1:$AN$1")<"Cat")*INDIRE CT("$E$3:$AN$3")) I need to add another criterium "Dog". How do I modify? |
#6
|
|||
|
|||
Hi
"JE McGimpsey" wrote in message ... The only advantage I can see to INDIRECT is that it allows one to insert columns/rows without the formula automatically adjusting. Then I would use dynamic named ranges instead. Arvi Laanemets |
#7
|
|||
|
|||
In article ,
"Arvi Laanemets" wrote: Then I would use dynamic named ranges instead. I might, too, but at the same time, it would introduce a layer of obfuscation that using INDIRECT() doesn't. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
referencing named formula using INDIRECT function | Excel Worksheet Functions | |||
sumproduct & indirect | Excel Worksheet Functions | |||
Indirect range in SUMPRODUCT? | Excel Discussion (Misc queries) | |||
Sumproduct function not working | Excel Worksheet Functions | |||
adding two sumproduct formulas together | Excel Worksheet Functions |