Sumproduct and Transpose Problem (double counting)

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
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,138
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
Joined
Nov 23, 2009
Messages
4
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?
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,138
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
Joined
Nov 23, 2009
Messages
4
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?
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,138
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
Joined
Nov 23, 2009
Messages
4
Terrific. Thanks again Aladin for your help.

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
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,138
Terrific. Thanks again Aladin for your help.

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.
 

Forum statistics

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

Some videos you may like

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...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top