Sumproduct multiple criteria

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Scott Kieta - 28 May 2008 23:17 GMT

Trying to count the # of times specific text shows up within a specific time
frame (i.e., "Product A" that was completed between 7 am and 8 am)

This is the formula i was provided to count the # of times an item shows up
between the specific time frame.

=SUMPRODUCT((HOUR($I$15:$I$350)>=7)*(HOUR($I$15:$I$350)<8))

Can i add a criteria to this to only count if the text equals "Product A"?

Thanks

Reply to this Message

Max - 28 May 2008 23:33 GMT

Something like this:
=SUMPRODUCT((HOUR($I$15:$I$350)>=7)*(HOUR($I$15:$I$350)<8)*($K$15:$K$350="Product A"))
where col K contains the product

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> Trying to count the # of times specific text shows up within a specific time
> frame (i.e., "Product A" that was completed between 7 am and 8 am)
[quoted text clipped - 7 lines]
>
> Thanks

Reply to this Message

Scott Kieta - 29 May 2008 00:26 GMT

I tried that formula and it became an array. I think i should have mentioned
the data i am gathering is on a different tab within same workbook. This is
the formula I entered.

=SUMPRODUCT((HOUR(NNC!F48:F198)>=20)*(HOUR(NNC!F48:F198))<21)*(NNC!$N$48:$N$199=B8)

It returned a value of 0, but when i count them manually should come up as 20.

In worksheet "NNC" column F is a date format column with mm/dd/yyyy h:mm
AM/PM and the column N is the Product type. "B8" is the 1st cell of a list of
product types that is used for validation for column N.

Did that make sense?

> Something like this:
> =SUMPRODUCT((HOUR($I$15:$I$350)>=7)*(HOUR($I$15:$I$350)<8)*($K$15:$K$350="Product A"))
[quoted text clipped - 10 lines]
> >
> > Thanks

Reply to this Message

Teethless mama - 29 May 2008 02:41 GMT

Keep in mind all columns have to equal size. Your column N is one row more
than column F.

> I tried that formula and it became an array. I think i should have mentioned
> the data i am gathering is on a different tab within same workbook. This is
[quoted text clipped - 24 lines]
> > >
> > > Thanks

Reply to this Message

Max - 29 May 2008 02:49 GMT

Your attempt:

> =SUMPRODUCT((HOUR(NNC!F48:F198)>=20)*(HOUR(NNC!F48:F198))<21)*(NNC!$N$48:$N$199=B8)

contains some inconsistencies, eg inconsistent parens, range size - "$N:$199"

Try this revision, with TRIM now used to improve robustness in matching:
=SUMPRODUCT((HOUR(NNC!F$48:F$198)>=20)*(HOUR(NNC!F$48:F$198)<21)*(TRIM(NNC!N$48:N$198)=TRIM(B8)))
(lightly tested ok here)

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> I tried that formula and it became an array. I think i should have mentioned
> the data i am gathering is on a different tab within same workbook. This is
[quoted text clipped - 9 lines]
>
> Did that make sense?

Reply to this Message

Scott Kieta - 29 May 2008 16:13 GMT

I appreciate the help from both of you this formula ended up working out once
the inconsistencies were fixed.

Thanks

> Your attempt:
> > =SUMPRODUCT((HOUR(NNC!F48:F198)>=20)*(HOUR(NNC!F48:F198))<21)*(NNC!$N$48:$N$199=B8)
[quoted text clipped - 16 lines]
> >
> > Did that make sense?

Reply to this Message

Max - 29 May 2008 20:44 GMT

Welcome. Take a moment to press the "Yes" buttons below. Give us the greens.

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> I appreciate the help from both of you this formula ended up working out once
> the inconsistencies were fixed.
>
> Thanks

Reply to this Message