Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with SUMPRODUCT Formula
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... |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with SUMPRODUCT Formula
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 "msbutton27" wrote: 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... |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with SUMPRODUCT Formula
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 ------------------------------------------------------------------------ pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261 View this thread: http://www.excelforum.com/showthread...hreadid=495073 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with SUMPRODUCT Formula
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 -- pinmaster ------------------------------------------------------------------------ pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261 View this thread: http://www.excelforum.com/showthread...hreadid=495073 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with SUMPRODUCT Formula
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. "msbutton27" wrote: 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... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Modify A SumProduct Formula | Excel Worksheet Functions | |||
sumproduct formula to slow | Excel Worksheet Functions | |||
SUMPRODUCT formula | Excel Worksheet Functions | |||
adding two sumproduct formulas together | Excel Worksheet Functions |