Sum if multiple condition

[popac]

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

 

[iggydarsa]

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

 

[Legacy 51064]

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

 

[iggydarsa]

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?

 

[Legacy 51064]

Sorry, I had an extra ")".



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

 

[Domenic]

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?