Sum if multiple condition

popac

New Member
Joined
Dec 5, 2005
Messages
2
I need help with a formula to calculate the sum of 20% of value in column F if the value in column E is 3 and the month in column C is Jan (resp. Feb Mar...). Sometimes the field in Column C is empty.
A B C D E F
1 01-Jan-05 3 $1500
2 05-Feb-05 3 $2500
3 02-Jan-05 2 $5000
4 09-Jan-05 3 $2000

When the value in column E is 2 I need to sum 30% of the value in F and check the month in column D.

Thank You
Cris
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

iggydarsa

Well-known Member
Joined
Jun 28, 2005
Messages
1,647
A B C D E F
1 01-Jan-05 3 $1500
2 05-Feb-05 3 $2500
3 02-Jan-05 2 $5000
4 09-Jan-05 3 $2000

When the value in column E is 2 I need to sum 30% of the value in F and check the month in column D.

It looks like column D has prices in it, not the months
 
L

Legacy 51064

Guest
=SUMPRODUCT((B1:B100="JAN")*(E1:E100=3)*(F1:F100)*.2))
 

iggydarsa

Well-known Member
Joined
Jun 28, 2005
Messages
1,647
A B C D E F
1 01-Jan-05 3 $1500
2 05-Feb-05 3 $2500
3 02-Jan-05 2 $5000
4 09-Jan-05 3 $2000

When the value in column E is 2 I need to sum 30% of the value in F and check the month in column D.

It looks like column D has prices in it, not the months... or are you dividing the date into 3 columns? if not where are the info in E and F? Also, what should happen if the date is blank?
 
L

Legacy 51064

Guest
Sorry, I had an extra ")".



=SUMPRODUCT((B1:B100="JAN")*(E1:E100=3)*(F1:F100)*0.2)
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,402
If a cell in Column C is empty, I assume that corresponding value in Column F is also empty. If this is correct, try...

=SUMPRODUCT(--(TEXT(C1:C4,"mmm")=H1),--(E1:E4=I1),F1:F4)*J1

...where H1 contains your first criterion, such as Jan, I1 contains your second criterion, such as 3, and J1 contains your third criterion, such as 20%. Otherwise, try the following instead...

=SUMPRODUCT(--(C1:C4<>""),--(TEXT(C1:C4,"mmm")=H1),--(E1:E4=I1),F1:F4)*J1

Did you want to include the year as part of your criteria?
 

Watch MrExcel Video

Forum statistics

Threads
1,118,328
Messages
5,571,564
Members
412,405
Latest member
DutchMonkey
Top