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
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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 <>).
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,144
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top