# Sumproduct and Transpose Problem (double counting)

#### David Breitenbach

##### New Member
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

##### MrExcel MVP
Try...

=SUMPRODUCT(--ISNA(MATCH(\$C\$6:\$C\$6,\$BD\$1:\$BD\$3,0)),\$G\$<WBR>6:\$G\$6)

although strange to use for a single-cell range.

#### David Breitenbach

##### New Member
This works - thanks. I used a one row range for simplicity in the explanation only. Can you explain why the transpose does not work in this case?

##### MrExcel MVP
This works - thanks. I used a one row range for simplicity in the explanation only.
You are welcome.

Can you explain why the transpose does not work in this case?
A value in C is evaluated against every value in the (transposed) range for non-equality. For example, if C6 is not equal to any of the 3-item (transposed) range, we'll get G6 three times, that is:

C6 <> Item-1 --> G6
C6 <> Item-2 --> G6
C6 <> Item-3 --> G6

while the intent is: if C6 is not in the transposed range, return G6 once.

An evaluation with ISNA/MATCH returns a single value: either TRUE or FALSE (the former corresponds to an #N/A). If TRUE, then G6, otherwise 0. The evaluation is then satisfied.

BTW, the TRANSPOSE call makes the formula require control+shift+enter.

#### David Breitenbach

##### New Member
Right - so the explanation of why it does work with the "=" qualifier instead of the "<>" qualifier is really that that won't always work either if, for example, there are more than one match in the qualifier range. Is that accurate?

##### MrExcel MVP
Right - so the explanation of why it does work with the "=" qualifier instead of the "<>" qualifier is really that that won't always work either if, for example, there are more than one match in the qualifier range. Is that accurate?
Right... When C6 = 2, the transposed range contains two 2's, and G6 = 7, we would get 14 if we had an equality test (= instead of <>).

#### David Breitenbach

##### New Member

I had been using the microsoft user groups for excel questions previously, but they are having a ton of password problems apparently, and of course you can't talk to them, so this will be my new spot for questions.

Dave

##### MrExcel MVP

I had been using the microsoft user groups for excel questions previously, but they are having a ton of password problems apparently, and of course you can't talk to them, so this will be my new spot for questions.

Dave
You are welcome.

1,082,114
Messages
5,363,244
Members
400,722
Latest member
DrewPop24

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...