Hi
I am currently using the following formula...
=SUMPRODUCT((INDIRECT("$E$1:$AN$1")<>"Cat")*INDIRECT("$E$3:$AN$3"))
I need to add another criterium "Dog". How do I modify?
Thanks
Rob
Hi
I am currently using the following formula...
=SUMPRODUCT((INDIRECT("$E$1:$AN$1")<>"Cat")*INDIRECT("$E$3:$AN$3"))
I need to add another criterium "Dog". How do I modify?
Thanks
Rob
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 <[email protected]>,
Rob <[email protected]> wrote:
> I am currently using the following formula...
> =SUMPRODUCT((INDIRECT("$E$1:$AN$1")<>"Cat")*INDIRECT("$E$3:$AN$3"))
> I need to add another criterium "Dog". How do I modify?
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 <[email protected]>,
> Rob <[email protected]> wrote:
>
> > I am currently using the following formula...
> > =SUMPRODUCT((INDIRECT("$E$1:$AN$1")<>"Cat")*INDIRECT("$E$3:$AN$3"))
> > I need to add another criterium "Dog". How do I modify?
>
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 <[email protected]>,
> Rob <[email protected]> wrote:
>
> > I am currently using the following formula...
> > =SUMPRODUCT((INDIRECT("$E$1:$AN$1")<>"Cat")*INDIRECT("$E$3:$AN$3"))
> > I need to add another criterium "Dog". How do I modify?
>
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 <[email protected]>,
Rob <[email protected]> wrote:
> I am currently using the following formula...
> =SUMPRODUCT((INDIRECT("$E$1:$AN$1")<>"Cat")*INDIRECT("$E$3:$AN$3"))
> I need to add another criterium "Dog". How do I modify?
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" <[email protected]> wrote in message
news:[email protected]...
> Hi
> I am currently using the following formula...
> =SUMPRODUCT((INDIRECT("$E$1:$AN$1")<>"Cat")*INDIRECT("$E$3:$AN$3"))
> I need to add another criterium "Dog". How do I modify?
> Thanks
> Rob
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" <[email protected]> wrote in message
news:[email protected]...
> Hi
> I am currently using the following formula...
> =SUMPRODUCT((INDIRECT("$E$1:$AN$1")<>"Cat")*INDIRECT("$E$3:$AN$3"))
> I need to add another criterium "Dog". How do I modify?
> Thanks
> Rob
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 <[email protected]>,
"Arvi Laanemets" <[email protected]> wrote:
> Why do you use INDIRECT here?
Hi
"JE McGimpsey" <[email protected]> wrote in message
news:[email protected]...
> 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
In article <#[email protected]>,
"Arvi Laanemets" <[email protected]> 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.
In article <#[email protected]>,
"Arvi Laanemets" <[email protected]> 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.
Hi
"JE McGimpsey" <[email protected]> wrote in message
news:[email protected]...
> 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
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 <[email protected]>,
"Arvi Laanemets" <[email protected]> wrote:
> Why do you use INDIRECT here?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks