|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
November 17th, 2003, 05:18 PM
|
|||
|
|||
Conditional Sum based upon text value
I have two columns of data.
Col A Col B 1W 10 2P 20 3G 30 15P 90 16G 5 18W 15 I want to summarize the values for W, P, G with the values in column B. How would I accomplish this? Total W = 25 Total P = 110 Total G = 35 TIA Mike 
|
|
#2
November 17th, 2003, 05:29 PM
|
|||
|
|||
|
Conditional Sum based upon text value
One way
=SUMPRODUCT(--ISNUMBER(FIND(C1,$A$2:$A$10)),--(B2:B10)) where C1 holds the criteria, hard coded for W it would look like =SUMPRODUCT(--ISNUMBER(FIND("W",$A$2:$A$10)),--(B2:B10)) adapt to fit your data -- Regards, Peo Sjoblom "Mike" wrote in message ... I have two columns of data. Col A Col B 1W 10 2P 20 3G 30 15P 90 16G 5 18W 15 I want to summarize the values for W, P, G with the values in column B. How would I accomplish this? Total W = 25 Total P = 110 Total G = 35 TIA Mike
|
|
#3
November 17th, 2003, 06:40 PM
|
|||
|
|||
|
Conditional Sum based upon text value
What is the purpose of the "--" in the formula?
-----Original Message----- One way =SUMPRODUCT(--ISNUMBER(FIND(C1,$A$2:$A$10)),--(B2:B10)) where C1 holds the criteria, hard coded for W it would look like =SUMPRODUCT(--ISNUMBER(FIND("W",$A$2:$A$10)),--(B2:B10)) adapt to fit your data -- Regards, Peo Sjoblom "Mike" wrote in message ... I have two columns of data. Col A Col B 1W 10 2P 20 3G 30 15P 90 16G 5 18W 15 I want to summarize the values for W, P, G with the values in column B. How would I accomplish this? Total W = 25 Total P = 110 Total G = 35 TIA Mike .
|
|
#4
November 17th, 2003, 06:56 PM
|
|||
|
|||
|
Conditional Sum based upon text value
"Peo Sjoblom" wrote...
One way =SUMPRODUCT(--ISNUMBER(FIND(C1,$A$2:$A$10)),--(B2:B10)) where C1 holds the criteria, hard coded for W it would look like =SUMPRODUCT(--ISNUMBER(FIND("W",$A$2:$A$10)),--(B2:B10)) adapt to fit your data Why either of these rather than =SUMIF(A2:A10,"*"&C1&"*",B2:B10) or =SUMIF(A2:A10,"*W*",B2:B10) respectively? -- Never attach files. Snip unnecessary quoted text. Never multipost (though crossposting is usually OK). Don't change subject lines because it corrupts Google newsgroup archives.
|
|
#5
November 17th, 2003, 07:05 PM
|
|||
|
|||
|
Conditional Sum based upon text value
True, definitely better to use sumif
-- Regards, Peo Sjoblom "Harlan Grove" wrote in message ... "Peo Sjoblom" wrote... One way =SUMPRODUCT(--ISNUMBER(FIND(C1,$A$2:$A$10)),--(B2:B10)) where C1 holds the criteria, hard coded for W it would look like =SUMPRODUCT(--ISNUMBER(FIND("W",$A$2:$A$10)),--(B2:B10)) adapt to fit your data Why either of these rather than =SUMIF(A2:A10,"*"&C1&"*",B2:B10) or =SUMIF(A2:A10,"*W*",B2:B10) respectively? -- Never attach files. Snip unnecessary quoted text. Never multipost (though crossposting is usually OK). Don't change subject lines because it corrupts Google newsgroup archives.
|
|
#6
November 17th, 2003, 07:59 PM
|
|||
|
|||
|
Conditional Sum based upon text value
How about if only the first four characters of the text
should be used for the conditional statement? -----Original Message----- "Peo Sjoblom" wrote... One way =SUMPRODUCT(--ISNUMBER(FIND(C1,$A$2:$A$10)),--(B2:B10)) where C1 holds the criteria, hard coded for W it would look like =SUMPRODUCT(--ISNUMBER(FIND("W",$A$2:$A$10)),--(B2:B10)) adapt to fit your data Why either of these rather than =SUMIF(A2:A10,"*"&C1&"*",B2:B10) or =SUMIF(A2:A10,"*W*",B2:B10) respectively? -- Never attach files. Snip unnecessary quoted text. Never multipost (though crossposting is usually OK). Don't change subject lines because it corrupts Google newsgroup archives. .
|
|
#7
November 17th, 2003, 08:13 PM
|
|||
|
|||
|
Conditional Sum based upon text value
Using the same example but I adding the other contents of
Col A. Poses a new problem using SUMIF... I have two columns of data. Col A Col B 1W Welfare 10 2P Welfare 20 3G 30 15P 90 16G 5 18W 15 I want to summarize the values for W, P, G with the values in column B. How would I accomplish this? Total W = 25 Total P = 110 Total G = 35 -----Original Message----- "Peo Sjoblom" wrote... One way =SUMPRODUCT(--ISNUMBER(FIND(C1,$A$2:$A$10)),--(B2:B10)) where C1 holds the criteria, hard coded for W it would look like =SUMPRODUCT(--ISNUMBER(FIND("W",$A$2:$A$10)),--(B2:B10)) adapt to fit your data Why either of these rather than =SUMIF(A2:A10,"*"&C1&"*",B2:B10) or =SUMIF(A2:A10,"*W*",B2:B10) respectively? -- Never attach files. Snip unnecessary quoted text. Never multipost (though crossposting is usually OK). Don't change subject lines because it corrupts Google newsgroup archives. .
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
