Conditional Sum based upon text value

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

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
 
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
 
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





.
Click to expand...
 
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
Click to expand...

Why either of these rather than

=SUMIF(A2:A10,"*"&C1&"*",B2:B10)

or

=SUMIF(A2:A10,"*W*",B2:B10)

respectively?
 
How about if only the first four characters of the text
should be used for the conditional statement?
 
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
 
Back
Top