Assistance with SUMPRODUCT function

[markytem]

Hi all,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
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></o>
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></o>
=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></o>
=SUMPRODUCT(--('Briefings'!$D$3:$D$999=D11),--('Briefings'!$G$3:$G$999=D12))<o></o>
This formula works when I test it manually, but I’m having difficulty in building the actual formula; <o></o>
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></o>
<o> </o>
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></o>
<o> </o>
Many thanks,<o></o>
Mark<o></o>

 

[Aladin Akyurek]

Are you seeing the formula itself in the cell instead of a result?

 

[markytem]

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]

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]

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]

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]

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]

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]

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]

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