Function SUMIF

  • Thread starter Thread starter Ricky
  • Start date Start date
R

Ricky

Hi guys

It's there any way I can us the function SUMIF with
multiple criteria or conditions on two different columns.
I'll appreciate your help.
 
Hi
no you can't use SUMIF with more than one criteria. Use SUMPRODUCT
instead
e.g. the following will sum column C based on the criteria in col. A
and col. B
=SUMPRODUCT((A1:A1000=criteria_1)*(B1:B1000=criteria_2),C1:C1000)
 
No. Use SUMPRODUCT.


Assume condition 1 in D1, Condition 2 in D2:

=SUMPRODUCT(--(A1:A1000=D1),--(B1:B1000=D2),C1:C1000)


will add all values in C1:C1000 for which the corresponding values in
column A = D1 and in column B = D2.
 
Thanks but It did not work. What I want to do is to add
the numeric values in column I due to the text criteria
on column A and due to the text criteria on column D.
Please help.
 
I tried bit it did not work. Since my criteria is text
and numeric I'm having an error.
 
Hi
just adapt the formula like
=SUMPRODUCT((A1:A1000="text1")*(D1:D1000="text2"),I1:I1000)

just replace text1 and text2 with your criteria
 
It worked, thanks Frank.
-----Original Message-----
Hi
just adapt the formula like
=SUMPRODUCT((A1:A1000="text1")* (D1:D1000="text2"),I1:I1000)

just replace text1 and text2 with your criteria


--
Regards
Frank Kabel
Frankfurt, Germany



.
 
Frank Kabel said:
Hi
no you can't use SUMIF with more than one criteria. Use SUMPRODUCT
instead
e.g. the following will sum column C based on the criteria in col. A
and col. B
=SUMPRODUCT((A1:A1000=criteria_1)*(B1:B1000=criteria_2),C1:C1000)

--
Regards
Frank Kabel
Frankfurt, Germany

Ricky wrote:
> Hi guys
>
> It's there any way I can us the function SUMIF with
> multiple criteria or conditions on two different columns.
> I'll appreciate your help.
Hi Frank;
I'd like to thank you so much,I was wonderýng about this formula for almost 5 hours and I checked every forum,You just explained it in the simplest way.

Kind Regards

Dilek
Istanbul,TURKEY
 
%5C%5CMy%20Documents%5CMy%20Pictures%5Cformula%20needed
C:%5CDocuments%20and%20Settings%5CHP.COM%5CMy%20Documents%5CMy%20Pictures
148.840 1-Jan Sales 89.304 1-Jan Sales 101.211 1-Jan
Transfer


89.304 1-Jan Sales 148.840 2-Jan Transfer 148.840 2-Jan
Sales 119.072 2-Jan Transfer 89.304 2-Jan Transfer 119.072 2-Jan Sales 119.072 4-Jan Transfer 148.840 4-Jan Transfer 327.448 4-Jan Sales 89.304 4-Jan Sales 148.840 6-Jan Transfer 59.536 6-Jan Transfer 59.536 6-Jan Sales we have to find below data Sales Transfer 1-Jan 2-Jan 3-Jan 4-Jan 5-Jan 6-Jan

please check this sheet and tell, can we fill the coloured cell with formula by sumif or other one.
if is this possible please feel free to call me

Thanks & Regards
Rajeev Agarwal
Cell - 9411680440, 9266423303
Mail: (e-mail address removed)
 
please check this sheet fill the coloured cell with formula

if is this possible than please send me this to my email id rajeev.agarwal1986 at gmail.com
or call me at 9266423303


148.840
1-Jan
Sales
89.304
1-Jan
Sales
101.211
1-Jan
Transfer
89.304
1-Jan
Sales
148.840
2-Jan
Transfer
148.840
2-Jan
Sales
119.072
2-Jan
Transfer
89.304
2-Jan
Transfer
119.072
2-Jan
Sales
119.072
4-Jan
Transfer
148.840
4-Jan
Transfer
327.448
4-Jan
Sales
89.304
4-Jan
Sales
148.840
6-Jan
Transfer
59.536
6-Jan
Transfer
59.536
6-Jan
Sales


we have to find below data


Sales
Transfer
1-Jan

2-Jan

3-Jan

4-Jan

5-Jan

6-Jan

 
148.840
1-Jan
Sales
89.304
1-Jan
Sales
101.211
1-Jan
Transfer
89.304
1-Jan
Sales
148.840
2-Jan
Transfer
148.840
2-Jan
Sales
119.072
2-Jan
Transfer
89.304
2-Jan
Transfer
119.072
2-Jan
Sales
119.072
4-Jan
Transfer
148.840
4-Jan
Transfer
327.448
4-Jan
Sales
89.304
4-Jan
Sales
148.840
6-Jan
Transfer
59.536
6-Jan
Transfer
59.536
6-Jan
Sales


we have to find below data


Sales
Transfer
1-Jan

2-Jan

3-Jan

4-Jan

5-Jan

6-Jan

 
Back
Top