Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I use the sumif if I have multiple criteria (i.e. greater . | Excel Worksheet Functions | |||
Multiple SUMIF Criteria | Excel Worksheet Functions | |||
SUMIF with Mutiple Ranges & Criteria | Excel Discussion (Misc queries) | |||
"criteria" in a sumif refering to the value in another cell | Excel Discussion (Misc queries) | |||
Can I use a cell reference in the criteria for the sumif function. | Excel Worksheet Functions |