Assistance with SUMPRODUCT function

markytem

New Member
Joined
Apr 4, 2011
Messages
10
Hi all,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
This is my first post, as I’ve been using this site as a valuable resource for a number of months, and now need to ask for some assistance.<o:p></o:p>
I have created a number of sheets, each with similar columns (date, topic, mechanism, host etc.). I have also created a summary sheet with a series of drop-lists where a corresponding column performs a COUNTIF:<o:p></o:p>
=COUNTIF('Briefings'!$F$3:$F$95,B5)
What I’d like to do on the summary page is allow a conditional element, i.e. an AND statement (Where Topic is ‘Sales’ and Company is ‘Microsoft’). To do this I’m trying to use a SUMPRODUCT function: <o:p></o:p>
=SUMPRODUCT(--('Briefings'!$D$3:$D$999=D11),--('Briefings'!$G$3:$G$999=D12))<o:p></o:p>
This formula works when I test it manually, but I’m having difficulty in building the actual formula; <o:p></o:p>
I tried to use a series of validation cells, named ranges and then concatenate. This however returns the value as TEXT and I am unable to use it as a formula.<o:p></o:p>
<o:p> </o:p>
Can anyone provide me any assistance in the syntax that I could use for SUMPRODUCT by using references to cells, rather than using literals as per my concatenation?<o:p></o:p>
<o:p> </o:p>
Many thanks,<o:p></o:p>
Mark<o:p></o:p>
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

markytem

New Member
Joined
Apr 4, 2011
Messages
10
Hi Aladin,
Thanks for the prompt response.

Yes, I am seeing the formula rather than the result. If I copy the formula cell and paste special (value) I get the correct formula, but when I press return <RETURN>I get this:
=CONCATENATE("=SUMPRODUCT(--(",#REF!,"=","""",#REF!,"""","),--(",#REF!,"=","""",#REF!,"""","))")


Mark
p.s. Should've mentioned this is Excel 2010
 

Weaver

Well-known Member
Joined
Sep 10, 2008
Messages
5,196
You ought to take a look at the =INDIRECT() function, as I think that may be of help in this instance. It allows you to create strings that represent cell addresses and then use these to refer to the values in the actual cells represented

EG

if Cell A1 contains 99

Cell B1 contains "A1"

then

=indirect(B1)

returns 99

HTH
 

markytem

New Member
Joined
Apr 4, 2011
Messages
10

ADVERTISEMENT

Thanks for the tip Weaver.
However, using INDIRECT(H32) returns a '#REF!' error. I'm assuming this is because the concatenation is text rather than a true formula?

Mark
 

markytem

New Member
Joined
Apr 4, 2011
Messages
10
Actually, I think 'INDIRECT' is the only way to be able to construct the elements of SUMPRODUCT without getting an error - Thanks for the guidance Weaver.

I think I managed to crack it with:
=SUMPRODUCT(--(INDIRECT("'Briefings'!"&$B$33)=D11),--(INDIRECT("'Brefings'!"&$E$33)=D12))
  • Where B33 is a complex IF statement returning column ranges based upon drop-list
  • Where D11 = Variable populated from drop-list
  • Where E33 is a complex IF statement returning column ranges based upon drop-list
  • Where D12 = Variable populated from drop-list
 

Weaver

Well-known Member
Joined
Sep 10, 2008
Messages
5,196

ADVERTISEMENT

Glad you got there in the end - I figured it would take too long to learn enough info to fully solve it for you, so I did the next best thing and handed you the fishing line, so to speak.
 

markytem

New Member
Joined
Apr 4, 2011
Messages
10
Thanks Weaver.
Next step is trying to work out why one selection returns a #REF! error, and another returns a #VALUE! error; would that be reserved characters such as a hypenated name?

Mark
 

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
Thanks Weaver.
Next step is trying to work out why one selection returns a #REF! error, and another returns a #VALUE! error; would that be reserved characters such as a hypenated name?

Mark
We'd need to see the formulas in B33 and E33 to see what they're doing.
 

markytem

New Member
Joined
Apr 4, 2011
Messages
10
Hi T. Valko,
The #REF! error was due to a misplaced ! character, so that error has gone.
The #VALUE! error was due to the end range reference field being blank.
Happy days!

However, another issue has risen!
How can I SUMPRODUCT with multivalue cells, ie, Col E could have multiple attendees:
David Smith
Fred Jones
John Dole

I can get a summary count by using the following:
=COUNTIF('Events'!$E$3:$E$93,("*"&H4&"*"))

Is it should be possible to use this in SUMPRODUCT/INDIRECT?

Thank you all.


Mark
 

Watch MrExcel Video

Forum statistics

Threads
1,109,331
Messages
5,528,056
Members
409,799
Latest member
camronmartin

This Week's Hot Topics

Top