David Breitenbach
New Member
- Joined
- Nov 23, 2009
- Messages
- 4
Here is my formula: {SUMPRODUCT(($C$6:$C$6<><wbr>TRANSPOSE($BD$1:$BD$3))*($G$<wbr>6:$G$6))}
I'm trying to use the range of bd1-3 as a qualifier in my sumproduct.
Textually, I'd like the formula to give me every instance where column C does not equal any of the items listed in BD1:bd3 and to sum up any corresponding entry in column G.
If I switch the formula so the qualifier is "=" instead of "<>" then I get what would be the correct answer for that qualifier, but the "<>" qualifier adds up the instances for every case in the range of BD1:bd3. I'm going to stop here and use an example as it's hard to describe:
let's say bd1:bd3= the following values: B;C;D
and that further c6=D
and that further g6=5
The first formula with "=" as a qualifier gives 5
The first formula with "<>" as a qualifier gives 10 (because it doesn't match B or C), when it should give 0 (since it is equal to at least on of the qualifying list)
Hope I described this well enough to get some help.
tia,
Dave
PS - must be entered as an array formula
I'm trying to use the range of bd1-3 as a qualifier in my sumproduct.
Textually, I'd like the formula to give me every instance where column C does not equal any of the items listed in BD1:bd3 and to sum up any corresponding entry in column G.
If I switch the formula so the qualifier is "=" instead of "<>" then I get what would be the correct answer for that qualifier, but the "<>" qualifier adds up the instances for every case in the range of BD1:bd3. I'm going to stop here and use an example as it's hard to describe:
let's say bd1:bd3= the following values: B;C;D
and that further c6=D
and that further g6=5
The first formula with "=" as a qualifier gives 5
The first formula with "<>" as a qualifier gives 10 (because it doesn't match B or C), when it should give 0 (since it is equal to at least on of the qualifying list)
Hope I described this well enough to get some help.
tia,
Dave
PS - must be entered as an array formula