Help With A Tricky SUMPRODUCT Function

[nevi]

Hello All,

What I currently have is:


=SUMPRODUCT(('Daily Tracker'!C4:C115="Aircard - Connectivity Issue")*('Daily Tracker'!B4:B115={"Ait-Hamou, Achour","Antoine, Louis-Bernard","Brassard, Yanik","Dawson, Ronald","De Gonzague, Gilles","Gavilan, Carlos","Hachey, Anthony","Maheux-Anctil, Pierre","Nguyen, Minh Trun","Ortega, Orlando","Salazar, Ramon","Smadja, Yves","St. Pierre, Luc","Thomas, Steve","Troufanov, Alexandre"}))

The idea here is that it is counting how many times "Aircard - Connectivity Issue" is selected in C4:C115 on the Daily Tracker Sheet, but only if the corosponding name in B4:B115 matches one of the names listed.

Now, all of those names listed there in quotes are also listed in cells D5:D20 of the same sheet. Currently, anytime I need to change the names for this(which is ofter, unfortunatley) I change this whole list. I'd like to find a way to tell it to compare against the list of names in D5:D35(leaves room for the list to grow).

Any ideas?

[Marcelo]

maybe if you use INDIRECT

"nevi" escreveu:

>
> Hello All,
>
> What I currently have is:
>
>
> =SUMPRODUCT(('Daily Tracker'!C4:C115="Aircard - Connectivity
> Issue")*('Daily Tracker'!B4:B115={"Ait-Hamou, Achour","Antoine,
> Louis-Bernard","Brassard, Yanik","Dawson, Ronald","De Gonzague,
> Gilles","Gavilan, Carlos","Hachey, Anthony","Maheux-Anctil,
> Pierre","Nguyen, Minh Trun","Ortega, Orlando","Salazar, Ramon","Smadja,
> Yves","St. Pierre, Luc","Thomas, Steve","Troufanov, Alexandre"}))
>
> The idea here is that it is counting how many times "Aircard -
> Connectivity Issue" is selected in C4:C115 on the Daily Tracker Sheet,
> but only if the corosponding name in B4:B115 matches one of the names
> listed.
>
> Now, all of those names listed there in quotes are also listed in cells
> D5:D20 of the same sheet. Currently, anytime I need to change the names
> for this(which is ofter, unfortunatley) I change this whole list. I'd
> like to find a way to tell it to compare against the list of names in
> D5:D35(leaves room for the list to grow).
>
> Any ideas?
>
>
> --
> nevi
> ------------------------------------------------------------------------
> nevi's Profile: http://www.excelforum.com/member.php...o&userid=33238
> View this thread: http://www.excelforum.com/showthread...hreadid=547242
>
>

[Bob Phillips]

I have responded in excel.misc

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"nevi" <[email protected]> wrote in message
news:[email protected]...
>
> Hello All,
>
> What I currently have is:
>
>
> =SUMPRODUCT(('Daily Tracker'!C4:C115="Aircard - Connectivity
> Issue")*('Daily Tracker'!B4:B115={"Ait-Hamou, Achour","Antoine,
> Louis-Bernard","Brassard, Yanik","Dawson, Ronald","De Gonzague,
> Gilles","Gavilan, Carlos","Hachey, Anthony","Maheux-Anctil,
> Pierre","Nguyen, Minh Trun","Ortega, Orlando","Salazar, Ramon","Smadja,
> Yves","St. Pierre, Luc","Thomas, Steve","Troufanov, Alexandre"}))
>
> The idea here is that it is counting how many times "Aircard -
> Connectivity Issue" is selected in C4:C115 on the Daily Tracker Sheet,
> but only if the corosponding name in B4:B115 matches one of the names
> listed.
>
> Now, all of those names listed there in quotes are also listed in cells
> D5:D20 of the same sheet. Currently, anytime I need to change the names
> for this(which is ofter, unfortunatley) I change this whole list. I'd
> like to find a way to tell it to compare against the list of names in
> D5:D35(leaves room for the list to grow).
>
> Any ideas?
>
>
> --
> nevi
> ------------------------------------------------------------------------
> nevi's Profile:
http://www.excelforum.com/member.php...o&userid=33238
> View this thread: http://www.excelforum.com/showthread...hreadid=547242
>