| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumif
I have checked and can't find anyone nesting sumif's by date and other
criteria. I have 5 columns that list (Production) a piece of equipment (multiples), the date(more multiples) for the same piece of equipment and then the quantity the equipment did. There could be 12 times the equipment is mentioned for a single day. I need to sum the quantity the piece of equipment did by the date. I have tried sumif(Production!A:A(date)=Summary!B1(specific date),sumif(Production!B:B=Summary!c1(equipment code),Production!C:C(quantity))) doesn't seem to like the nested if's. What else can I do? Thanks |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumif
Look at SUMPRODUCT:
=SUMPRODUCT(--(Production!A2:A100)="specific date"),--(Production!B2:B100)="production code"),Production!C2:C100) Set Date (H1) and code (H2) in cells: e.g. =SUMPRODUCT(--(Production!A2:A100)=H1),--(Production!B2:B100)=H2),Production!C2:C100) Except in XL2007, ranges cannot be a whole column AND must be same size for all ranges. HTH "Arlene" wrote: I have checked and can't find anyone nesting sumif's by date and other criteria. I have 5 columns that list (Production) a piece of equipment (multiples), the date(more multiples) for the same piece of equipment and then the quantity the equipment did. There could be 12 times the equipment is mentioned for a single day. I need to sum the quantity the piece of equipment did by the date. I have tried sumif(Production!A:A(date)=Summary!B1(specific date),sumif(Production!B:B=Summary!c1(equipment code),Production!C:C(quantity))) doesn't seem to like the nested if's. What else can I do? Thanks |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumif
Hi Arlene,
Not seeing your formula as you have actually typed it makes it more difficult but dates and sumif are handled as below:- =SUMIF(A1:A5,"<="&DATE(2004,4,2),B1:B5) You can't simply enter a date as criteria. Mike "Arlene" wrote: I have checked and can't find anyone nesting sumif's by date and other criteria. I have 5 columns that list (Production) a piece of equipment (multiples), the date(more multiples) for the same piece of equipment and then the quantity the equipment did. There could be 12 times the equipment is mentioned for a single day. I need to sum the quantity the piece of equipment did by the date. I have tried sumif(Production!A:A(date)=Summary!B1(specific date),sumif(Production!B:B=Summary!c1(equipment code),Production!C:C(quantity))) doesn't seem to like the nested if's. What else can I do? Thanks |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumif
This is actually what I entered and I get 0, I have entered it as a ctrl,
shift and enter and still get zero. What can I be doing wrong. Thanks =SUMPRODUCT((B4:B9759="02/01/2007")*(C4:C9759="ZL118")*F4:F9759) B C D E F DATE_OUT EQUIPMENT_CODE PIECE_COUNT SETTING VOLUME 02/01/2007 ZL118 62 I014 48.81 02/01/2007 ZL118 159 I014 39.46 02/01/2007 ZL118 178 I014 44.12 02/01/2007 DB1 131 J140 34.36 "Toppers" wrote: Look at SUMPRODUCT: =SUMPRODUCT(--(Production!A2:A100)="specific date"),--(Production!B2:B100)="production code"),Production!C2:C100) Set Date (H1) and code (H2) in cells: e.g. =SUMPRODUCT(--(Production!A2:A100)=H1),--(Production!B2:B100)=H2),Production!C2:C100) Except in XL2007, ranges cannot be a whole column AND must be same size for all ranges. HTH "Arlene" wrote: I have checked and can't find anyone nesting sumif's by date and other criteria. I have 5 columns that list (Production) a piece of equipment (multiples), the date(more multiples) for the same piece of equipment and then the quantity the equipment did. There could be 12 times the equipment is mentioned for a single day. I need to sum the quantity the piece of equipment did by the date. I have tried sumif(Production!A:A(date)=Summary!B1(specific date),sumif(Production!B:B=Summary!c1(equipment code),Production!C:C(quantity))) doesn't seem to like the nested if's. What else can I do? Thanks |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumif
try:
=SUMPRODUCT((B4:B9759=DATEVALUE("02/01/2007"))*(C4:C9759="ZL118")*F4:F9759) Just ENTER (no need for Ctrl+Shift+Enter) "Arlene" wrote: This is actually what I entered and I get 0, I have entered it as a ctrl, shift and enter and still get zero. What can I be doing wrong. Thanks =SUMPRODUCT((B4:B9759="02/01/2007")*(C4:C9759="ZL118")*F4:F9759) B C D E F DATE_OUT EQUIPMENT_CODE PIECE_COUNT SETTING VOLUME 02/01/2007 ZL118 62 I014 48.81 02/01/2007 ZL118 159 I014 39.46 02/01/2007 ZL118 178 I014 44.12 02/01/2007 DB1 131 J140 34.36 "Toppers" wrote: Look at SUMPRODUCT: =SUMPRODUCT(--(Production!A2:A100)="specific date"),--(Production!B2:B100)="production code"),Production!C2:C100) Set Date (H1) and code (H2) in cells: e.g. =SUMPRODUCT(--(Production!A2:A100)=H1),--(Production!B2:B100)=H2),Production!C2:C100) Except in XL2007, ranges cannot be a whole column AND must be same size for all ranges. HTH "Arlene" wrote: I have checked and can't find anyone nesting sumif's by date and other criteria. I have 5 columns that list (Production) a piece of equipment (multiples), the date(more multiples) for the same piece of equipment and then the quantity the equipment did. There could be 12 times the equipment is mentioned for a single day. I need to sum the quantity the piece of equipment did by the date. I have tried sumif(Production!A:A(date)=Summary!B1(specific date),sumif(Production!B:B=Summary!c1(equipment code),Production!C:C(quantity))) doesn't seem to like the nested if's. What else can I do? Thanks |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sumif
Works like a charm, I did make my formats the same but obviously not enough.
Thanks so much. "Toppers" wrote: try: =SUMPRODUCT((B4:B9759=DATEVALUE("02/01/2007"))*(C4:C9759="ZL118")*F4:F9759) Just ENTER (no need for Ctrl+Shift+Enter) "Arlene" wrote: This is actually what I entered and I get 0, I have entered it as a ctrl, shift and enter and still get zero. What can I be doing wrong. Thanks =SUMPRODUCT((B4:B9759="02/01/2007")*(C4:C9759="ZL118")*F4:F9759) B C D E F DATE_OUT EQUIPMENT_CODE PIECE_COUNT SETTING VOLUME 02/01/2007 ZL118 62 I014 48.81 02/01/2007 ZL118 159 I014 39.46 02/01/2007 ZL118 178 I014 44.12 02/01/2007 DB1 131 J140 34.36 "Toppers" wrote: Look at SUMPRODUCT: =SUMPRODUCT(--(Production!A2:A100)="specific date"),--(Production!B2:B100)="production code"),Production!C2:C100) Set Date (H1) and code (H2) in cells: e.g. =SUMPRODUCT(--(Production!A2:A100)=H1),--(Production!B2:B100)=H2),Production!C2:C100) Except in XL2007, ranges cannot be a whole column AND must be same size for all ranges. HTH "Arlene" wrote: I have checked and can't find anyone nesting sumif's by date and other criteria. I have 5 columns that list (Production) a piece of equipment (multiples), the date(more multiples) for the same piece of equipment and then the quantity the equipment did. There could be 12 times the equipment is mentioned for a single day. I need to sum the quantity the piece of equipment did by the date. I have tried sumif(Production!A:A(date)=Summary!B1(specific date),sumif(Production!B:B=Summary!c1(equipment code),Production!C:C(quantity))) doesn't seem to like the nested if's. What else can I do? Thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| sumif for multi conditions. i.e sumif(A1:A10,"Jon" and B1:B10,"A" | Excel Worksheet Functions | |||
| Embedding a Sumif in a sumif | Excel Worksheet Functions | |||
| Sumif? | Excel Discussion (Misc queries) | |||
| nested sumif or sumif with two criteria | Excel Worksheet Functions | |||
| SUMIF - Range name to used for the "sum_range" portion of a SUMIF function | Excel Worksheet Functions | |||
Linear Mode
