Sumproduct multiple criteria

[Scott Kieta]

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

 

[Max]

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

 

[Scott Kieta]

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?

 

[Teethless mama]

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

 

[Max]

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)

---

 

[Scott Kieta]

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

Thanks

 

[Max]

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