Help with SUMPRODUCT Formula

[msbutton27]

Have to worksheets, one with a bunch of raw data and the other where I am
performing my calculations.

Therefore in RAW DATA sheet I have 2 columns that look like this

Column A Column B
A Latitude
B Compaq
A Gateway
C GX250
A Gateway
B Latitude
A GX270
C Compaq

So I want to perform a calculation that:
If Column A equals A, then count all the Compaq's
If Column A equals A, then count allthe GX270
And so forth, basically depending on Column A I need to count words
instances in column B.

Column B, does not always contian the exact word, sometimes it may say
Latitude C610, or C620 - therefore want to count the instances of the word
"Latitude" no matter what version...

I was trying to use this:

=SUMPRODUCT(('--(Raw Data'!K:K=A)),(--('Raw Data'!L:L="Latitude")))
but of course does not work - any thoughts...

 

[Ron Coderre]

I think what you may need is a 2-column list of all possible Col_L values and
their translated value:
Latitude C610 Latitude
C620 Latitude
etc

Then, put this formula in M2 and copy down
=VLOOKUP(L2,your_list_range,2,0)
-->replace your_list_range with your actual list range

Last, change your formula to this:
=SUMPRODUCT((--('Raw Data'!$K$1:$K$65536=A)),(--('Raw
Data'!$M$1:$M$6536="Latitude")))

Of course, adjust range references to suit your situation.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

 

[pinmaster]

Hi, try this:

=SUMPRODUCT(--(A1:A100="A"),--(LEFT(B1:B100,8)="latitude"))

=SUMPRODUCT(--(A1:A100="A"),--(LEFT(B1:B100,6)="compaq"))

=SUMPRODUCT(--(A1:A100="A"),--(LEFT(B1:B100,7)="gateway"))

and so on....

HTH
JG

 

[pinmaster]

or even

Col(F)
latitude
compaq
gateway
GX250

etc....

in Col(G) starting on the same row as Col(F) put

=SUMPRODUCT(--($A$1:$A$100="A"),--(LEFT($B$1:$B$100,LEN(F1))=F1))
copied down



note: do not use hole columns as your reference A:A use something
like $A$1:$A$100


Regards
JG

 

[JMB]

one other possibility:

=SUMPRODUCT(--('raw data'!K1:K10="A"),--(NOT(ISERROR(SEARCH("Latitude",'raw
data'!L1:L10,1)>0))))

FYI - I believe Ron and Pinmaster pointed out the sumproduct cannot work
with an entire column so K:K will not work, but K1:K65535 will.