sumif

[snjpverma]

i have a names in column A and numbers in column B. Now i want to get the total of john. Then I put the below formula
=SUMIF(A:A,"john",B:B)
This gives my solution. But when I want the total of two or more people. Then it's a problem.
I am trying it like this.
=SUMIF(A:A,"john",B:B)+SUMIF(A:A,"mark",B:B)+SUMIF(A:A,"john",B:B)
This seems to be quite long because i have to put the whole formula again and select the same ranges again. Is there any short formula for this.

 

[Dave Patton]

Excel Workbook
	A	B	C	D	E	F
1	Names	Amount				1,000.00
2	John	240.00	Criteria >	John	Mark	1,000.00
3	Bill	280.00
4	Mark	320.00
5	Jane	360.00
6	Kristi	400.00
7	John	440.00
1a

Excel 2003

Cell Formulas
Range	Formula
F1	=SUM(SUMIF($A$2:$A$10,{"John","Mark"},B$2:B$10))
F2	=SUMPRODUCT(SUMIF(A2:A10,D2:E2,B2:B10))

try either of the formulas above

N.B. The Criteria is in D2 and E2

 

[T. Valko]

i have a names in column A and numbers in column B. Now i want to get the total of john. Then I put the below formula
=SUMIF(A:A,"john",B:B)
This gives my solution. But when I want the total of two or more people. Then it's a problem.
I am trying it like this.
=SUMIF(A:A,"john",B:B)+SUMIF(A:A,"mark",B:B)+SUMIF(A:A,"john",B:B)
This seems to be quite long because i have to put the whole formula again and select the same ranges again. Is there any short formula for this.

Here's another one...

Book1
	A	B	C	D
1	Name	Value
2	Bill	89		Tom
3	Tom	13		Sue
4	Sue	91		Biff
5	Bill	50
6	Bill	86
7	Tom	16
8	Sue	88
9	Sue	79
10	Sue	36
11	Lisa	66
12	Janet	59
13	Biff	59
14	Biff	80
15	Carl	2
Sheet1

To get the sum for Tom, Sue and Biff...

=SUMPRODUCT(--(ISNUMBER(MATCH(A2:A15,D2:D4,0))),B2:B15)

 

[snjpverma]

Both the replies are wonderful. thanks so much. But sorry that I forgot to add one thing. I have the dates in column C so if I want the to calculate the data only for a particular date. I mean to say, for example the sum for john and mark only for 4th april' 2011.

 

[Dave Patton]

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Names	Group	Amount	Date	Criteria >	A	Apr 04, 2011	1,200.00
2	John	A	240.00	Apr 01, 2011	Criteria >	John	Mark	1,200.00
3	Bill	B	280.00	Apr 02, 2011				1,200.00
4	Mark	A	320.00	Apr 04, 2011
5	Jane	B	360.00	Apr 04, 2011		Sum of Amount	Group
6	Kristi	B	400.00	Apr 04, 2011		Date	A	B	Grand Total
7	John	A	440.00	Apr 04, 2011		Apr 01, 2011	240.00		240.00
8	Jane	B	360.00	Apr 04, 2011		Apr 02, 2011		280.00	280.00
9	Kristi	B	400.00	Apr 04, 2011		Apr 04, 2011	1,200.00	1,880.00	3,080.00
10	John	A	440.00	Apr 04, 2011		Apr 05, 2011	440.00	1,160.00	1,600.00
11	Jane	B	360.00	Apr 04, 2011		Grand Total	1,880.00	3,320.00	5,200.00
12	Kristi	B	400.00	Apr 05, 2011
13	John	A	440.00	Apr 05, 2011
14	Jane	B	360.00	Apr 05, 2011
15	Kristi	B	400.00	Apr 05, 2011
16
1aa

Excel 2003

Cell Formulas
Range	Formula
H1	=SUMPRODUCT(--(B2:B15=F1),--(D2:D15=G1),(C2:C15))
H2	=SUMPRODUCT(--((A2:A15=F2)+(A2:A15=G2)),--(D2:D15=G1),(C2:C15))
H3	=SUMPRODUCT(--(ISNUMBER(MATCH(A2:A15,F2:G2,0))),--(D2:D15=G1),(C2:C15))

I inserted a Column for the Group information that I used in one formula and the Pivot table.

You may want to experiment with Pivot Tables.

 

[T. Valko]

Both the replies are wonderful. thanks so much. But sorry that I forgot to add one thing. I have the dates in column C so if I want the to calculate the data only for a particular date. I mean to say, for example the sum for john and mark only for 4th april' 2011.

Try this...

Book1
	A	B	C	D	E
1	Name	Value	Date		Criteria
2	Bill	89	4/1/2011		John
3	John	13	4/4/2011		Mark
4	Sue	91	4/4/2011		4/4/2011
5	Mark	50	4/3/2011
6	Mark	86	4/4/2011
7	Tom	16	4/6/2011
8	John	88	4/4/2011
9	John	79	4/4/2011
10	Sue	36	4/3/2011
11	Mark	66	4/1/2011
12	Janet	59	4/8/2011
13	Biff	59	4/8/2011
14	Biff	80	4/6/2011
15	John	2	4/3/2011
Sheet1

=SUMPRODUCT(--(ISNUMBER(MATCH(A2:A15,E2:E3,0))),--(C2:C15=E4),B2:B15)