excel formula for Sum based on conditions

Microsoft Windows vista home premium 64-...
March 3, 2010 at 07:34:20
Specs: Windows Vista, 1014MB
Hi
I have in column B three different types of data marked as S M and PD, and some other taht i don't need to calculate. in the next column (C) I have the numbers (35, 23 etc) Is it possible to create the formula that adds up the total form C column if anything in a B column equals S, then total from C column if anything in B column = M and last total from C if B column = PD?

See More: excel formula for Sum based on conditions

Report •


#1
March 3, 2010 at 08:43:48
The =SUMIF function is what your looking for:

B	C
s	35
m	23
pd	12
s	35
m	23
pd	12

In Cell C7 but the formula: =SUMIF(B1:B6,"s",C1:C6)
and you will get the sum of all the S letters,
In Cell C8 put the formula: =SUMIF(B1:B6,"m",C1:C6)
and you will get the sum of all the M letters.

You should be able to figure out the rest.

MIKE

http://www.skeptic.com/


Report •

#2
March 3, 2010 at 09:27:32
Thank you Mike for your help. That's brilliant and helped me a lot! and it is easy :)

Report •

#3
March 3, 2010 at 10:26:21
Glad I could help.

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
March 4, 2010 at 07:44:17
Could you tell me what formula I should use if i have two conditions, i.e. if anything in column A = "in" and anything in column B= "S" add up numbers in column C? and also would it be possible to have those results in a sheet number 2 instead of same sheet with data?

Thanks for helping me out! really apreciate!

Kat


Report •

#5
March 4, 2010 at 10:18:23
You will need an array formula.

When you enter the formula you must use the
CTRL - SHIFT - ENTER combination, else it won't work.

A	B	C
in	s	35
out	m	23
in	pd	12
out	s	35
in	m	23
out	pd	12

In Cell C7 put the formula:

=SUM( ( A2:A7 = "in" ) * ( B2:B7 ="s") * C2:C7 )

and you will get the sum of all the S letters in column B,
that also have the word IN in column A
You should be able to figure out the rest.

Again, this is an array formula, use CTRL - SHIFT - ENTER

Also, for new questions, start a new thread.
Thanks.

MIKE

http://www.skeptic.com/


Report •

#6
March 4, 2010 at 10:26:34
Just remembered you wanted this on sheet two.

On Sheet 2 in Cell A1 enter this revised formula:

=SUM( ( Sheet1!A2:A7 = "in" ) * ( Sheet1!B2:B7 ="s") * Sheet1!C2:C7 )

MIKE

http://www.skeptic.com/


Report •

#7
March 5, 2010 at 01:37:37
Great thank you so much for your help

Report •


Ask Question