Sumif

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Arlene - 18 Jun 2007 18:50 GMT

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 to this Message

Toppers - 18 Jun 2007 19:03 GMT

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

> I have checked and can't find anyone nesting sumif's by date and other
> criteria.
[quoted text clipped - 8 lines]
> code),Production!C:C(quantity))) doesn't seem to like the nested if's. What
> else can I do? Thanks

Reply to this Message

Arlene - 18 Jun 2007 19:36 GMT

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

> Look at SUMPRODUCT:
>
[quoted text clipped - 24 lines]
> > code),Production!C:C(quantity))) doesn't seem to like the nested if's. What
> > else can I do? Thanks

Reply to this Message

Toppers - 18 Jun 2007 19:49 GMT

try:

=SUMPRODUCT((B4:B9759=DATEVALUE("02/01/2007"))*(C4:C9759="ZL118")*F4:F9759)

Just ENTER (no need for Ctrl+Shift+Enter)

> 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
[quoted text clipped - 36 lines]
> > > code),Production!C:C(quantity))) doesn't seem to like the nested if's. What
> > > else can I do? Thanks

Reply to this Message

Arlene - 18 Jun 2007 20:10 GMT

Works like a charm, I did make my formats the same but obviously not enough.
Thanks so much.

> try:
>
[quoted text clipped - 42 lines]
> > > > code),Production!C:C(quantity))) doesn't seem to like the nested if's. What
> > > > else can I do? Thanks

Reply to this Message

Mike H - 18 Jun 2007 19:08 GMT

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

Reply to this Message