SUMIF with four criteria
 

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

SUMIF with four criteria
 

SUMIF is a wrong choice. Use SUMPRODUCT instead

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


"Ken" wrote:

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

SUMIF with four criteria
 

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),D2:D100)

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

On 30 Jan, 03:01, Ken wrote:
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