SUMIF with only Month

[Guest]

Hello,
I want make sum, detail is as under.
I want sum if A/Coulm month=JAN, B/Coulm=Car, C/Coulm Amount

A B C
01-JAN-2006 CAR 500
05-JAN-2006 CAT 100
01-FEB-2006 BOOK 400
=SUMIF (A1:A10 "JAN" & B1:B10 "CAR",C1:C10)
Thanks in advance.

 

[Ardus Petus]

=SUMPRODUCT((MONTH(A1:A10)=1)*(B1:B10="CAR"),C1:C10)

HTH

 

[Bruno Campanini]

Hello,
I want make sum, detail is as under.
I want sum if A/Coulm month=JAN, B/Coulm=Car, C/Coulm Amount

A B C
01-JAN-2006 CAR 500
05-JAN-2006 CAT 100
01-FEB-2006 BOOK 400
=SUMIF (A1:A10 "JAN" & B1:B10 "CAR",C1:C10)
Thanks in advance.

=SUMPRODUCT((A1:A10="JAN")*(B1:B10="CAR"),C1:C10)

Bruno

 

[Guest]

Try this:

With
Col_A containing dates
Col_B containing categories
Col_C containing numbers

This formula returns total CAR expenses for JAN:
G1: =SUMPRODUCT((TEXT(A1:A100,"mmm")="JAN")*(B1:B100="CAR")*C1:C100)

OR...this formula gives you some flexibility in looking up expenses:
E1: JAN
F1: CAR
G1: =SUMPRODUCT((TEXT(A1:A100,"mmm")=E1)*(B1:B100=F1)*C1:C100)

Adjust range references to suit your situation.

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Guest]

thank you very much.

=SUMPRODUCT((A1:A10="JAN")*(B1:B10="CAR"),C1:C10)

Bruno

 

[Guest]

yes it's working, thank you very much.

Try this:

With
Col_A containing dates
Col_B containing categories
Col_C containing numbers

This formula returns total CAR expenses for JAN:
G1: =SUMPRODUCT((TEXT(A1:A100,"mmm")="JAN")*(B1:B100="CAR")*C1:C100)

OR...this formula gives you some flexibility in looking up expenses:
E1: JAN
F1: CAR
G1: =SUMPRODUCT((TEXT(A1:A100,"mmm")=E1)*(B1:B100=F1)*C1:C100)

Adjust range references to suit your situation.

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Guest]

thank you very much.

=SUMPRODUCT((MONTH(A1:A10)=1)*(B1:B10="CAR"),C1:C10)

HTH