SUMIF with four criteria

[Ken]

Hi!

I would like to use SUMIF to get the total invoce amount for each job in,
for example, Jan., 07. In other words, I will type in the job number, month,
and year to get the total invoice amount. The layout of my data is as
follows:

Job# Date TypeOfTransaction Amount
1000 12/25/06 New Job $100
999 12/29/06 Invoice -$20
1000 01/02/07 Invoice -$50
1111 01/05/07 New Job $130
999 01/25/07 Invoice -$90
1000 02/01/07 Invoice -$50

Please note some data lines are not invoice-realted but Job/Contract amount.
(Please see the coulumn of "TypeOfTransaction")
Also, I can create more columns, if necessary, for example, "Year" and
"Month".

Thank you very much for your help!
Ken

 

[Teethless mama]

SUMIF is a wrong choice. Use SUMPRODUCT instead

=SUMPRODUCT(--(A2:A100="your job #"),--(TEXT(B2:B100,"mmm yy")="Jan
07"),D2100)

 

[RichardSchollar]

Hi Ken

As TM says, SumProduct is your best bet. However, I believe you
wanted to restrict to Invoices only, so you would need:

=SUMPRODUCT(--(A2:A100=E1),--(TEXT(B2:B100,"mmmyyyy")=F1),--
(C2:C100=G1),D2100)

This assumes that in E1 you have your relevant job number (eg 1000),
F1 you have the particular month/year combination in text format (eg
Jan2007), and in G1 you have the word Invoice.

Note that (unless you are using xl2007) you can't use whole column
references in Sumproduct (unlike you can in SUMIF where
SUMIF(A:A,">0") is possible for example), so you need to type in the
actual range (or use a defined name).

Hope this helps!

Richard