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>

 

[T. Valko]

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

SUMPRODUCT doesn't support wildcards.

You'd do something like this:

=SUMPRODUCT(--(ISNUMBER(SEARCH(H4,Events!E3:E93))))

 

[markytem]

Column E multivalue cell example:
E1=
David Smith
Fred Jones
John Dole
E2=
Fred Jones
E3=
Tom Thumb
David Smith
Fred Jones
E4=
John Dole
Fred Jones

Therefore a COUNTIF on:
David Smith = 2
Fred Jones = 4


Mark

 

[T. Valko]

Column E multivalue cell example:
E1=
David Smith
Fred Jones
John Dole
E2=
Fred Jones
E3=
Tom Thumb
David Smith
Fred Jones
E4=
John Dole
Fred Jones

Therefore a COUNTIF on:
David Smith = 2
Fred Jones = 4


Mark

H4 = David Smith

=SUMPRODUCT(--(ISNUMBER(SEARCH(H4,E1:E10))))

Result = 2

 

[markytem]

Thank you...
Can I use the ISNUMBER/SEARCH as part of the condition in my original SUMPRODUCT:
=SUMPRODUCT(--(INDIRECT("'Events'!"&$B$32)=J11),--(INDIRECT("'Events'!"&$E$32)=J12))

Mark

 

[T. Valko]

Thank you...
Can I use the ISNUMBER/SEARCH as part of the condition in my original SUMPRODUCT:
=SUMPRODUCT(--(INDIRECT("'Events'!"&$B$32)=J11),--(INDIRECT("'Events'!"&$E$32)=J12))

Mark

What's in E32?

This is the syntax as long as E32 evaluates to the correct range reference.

=SUMPRODUCT(--(INDIRECT("'Events'!"&$B$32)=J11),--(ISNUMBER(SEARCH(H4,INDIRECT("'Events'!"&$E$32)))))

 

[markytem]

E32 is a cell that returns a column range based upon a drop-list:
=IF(H11="E_AnalystHouse","$B$3:$B$999",IF(H11="E_Medium","$C$3:$C$999",IF(H11="E_Attendee","$E$3:$E$999","")))

I will give that a go, many thanks.

Mark

 

[markytem]

Many thanks to all that helped.
This is now working as I'd hoped.

Thank you.

 

[T. Valko]

Many thanks to all that helped.
This is now working as I'd hoped.

Thank you.

You're welcome. Thanks for the feedback!