I have a table of information that I need to simplify into a summary table.

Column B = Name
Column F= Time
Column H = Status

I need to count all of the the items that have a time of greater than 24
hours and all of the times that have a time of less than 24 hours. To
further complicate things, I need to count by individual names.

My Hope is to have the final table look something like this:

Billie (Under 24 Hours) [Number]
Billie (Over 24 Hours) [Number]

Jeremy (Under 24 Hours) [Number]
Jeremy (Over 24 hours) [Number]
There will also be names that are not on the list. I need those to return
Zero.

Please help.

Assume your summary table starts with names in X2 on the same sheet,
and that you have 250 rows of data starting on row 2. It might look
better if you used column Y for under 24 hours and column Z for
greater than 24 hours, so put this formula in Y2:

=SUMPRODUCT(($B$2:$B$250=$X2)*($F$2:$F$250<1))

and this in Z2:

=SUMPRODUCT(($B$2:$B$250=$X2)*($F$2:$F$250=1))

Note the similarity of the formulae - you can copy from Y2 into Z2 and
just amend the end part. Adjust the ranges from 250 if you have more
data than this.

Not sure why you told us that column H is status, as we haven't used
that.

Hope this helps.

Pete

On Feb 11, 5:16*pm, Jeremy <jeremiah.a.reynolds @ gmail.com wrote:
I have a table of information that I need to simplify into a summary table..

Column B = Name
Column F= Time
Column H = Status

I need to count all of the the items that have a time of greater than 24
hours and all of the times that have a time of less than 24 hours. *To
further complicate things, I need to count by individual names.

My Hope is to have the final table look something like this:

Billie (Under 24 Hours) * * [Number]
Billie (Over 24 Hours) * [Number]

Jeremy (Under 24 Hours) * * [Number]
Jeremy (Over 24 hours) * * [Number]
There will also be names that are not on the list. *I need those to return
Zero.

Please help.

I see what you're doing here, but I don't think it will give me what I need.
I am looking for how many times does the Name 'Billie" appear in Column B
when The corresponding cell in Column F is (greater and less than(2
formulas)) 24 and the corresponding cell in column H is Z9, I don't need
information on the other status after a second look.

"Pete_UK" wrote:

Assume your summary table starts with names in X2 on the same sheet,
and that you have 250 rows of data starting on row 2. It might look
better if you used column Y for under 24 hours and column Z for
greater than 24 hours, so put this formula in Y2:

=SUMPRODUCT(($B$2:$B$250=$X2)*($F$2:$F$250<1))

and this in Z2:

=SUMPRODUCT(($B$2:$B$250=$X2)*($F$2:$F$250=1))

Note the similarity of the formulae - you can copy from Y2 into Z2 and
just amend the end part. Adjust the ranges from 250 if you have more
data than this.

Not sure why you told us that column H is status, as we haven't used
that.

Hope this helps.

Pete

On Feb 11, 5:16 pm, Jeremy <jeremiah.a.reynolds @ gmail.com wrote:
I have a table of information that I need to simplify into a summary table..

Column B = Name
Column F= Time
Column H = Status

I need to count all of the the items that have a time of greater than 24
hours and all of the times that have a time of less than 24 hours. To
further complicate things, I need to count by individual names.

My Hope is to have the final table look something like this:

Billie (Under 24 Hours) [Number]
Billie (Over 24 Hours) [Number]

Jeremy (Under 24 Hours) [Number]
Jeremy (Over 24 hours) [Number]
There will also be names that are not on the list. I need those to return
Zero.

Please help.

Did you try the formulae?

If your times are in Excel time format, then 24 hours equates to a
value of 1, as times are stored internally as fractions of a 24-hour
day. Thus, comparing column F values with 1 is the same as comparing
them with 24 hours, and the other comparison is checking on the names
in column B being the same as the name in X2. When the formula is
copied down this changes to X3, X4 etc, i.e. it will take in the
different names you have listed (once) in column X.

Try it and see.

Pete

On Feb 11, 5:46*pm, Jeremy <jeremiah.a.reynolds @ gmail.com wrote:
I see what you're doing here, but I don't think it will give me what I need. *
I am looking for how many times does the Name 'Billie" appear in Column B
when The corresponding cell in Column F is (greater and less than(2
formulas)) 24 and the corresponding cell in column H is Z9, I don't need
information on the other status after a second look.



"Pete_UK" wrote:
Assume your summary table starts with names in X2 on the same sheet,
and that you have 250 rows of data starting on row 2. It might look
better if you used column Y for under 24 hours and column Z for
greater than 24 hours, so put this formula in Y2:

=SUMPRODUCT(($B$2:$B$250=$X2)*($F$2:$F$250<1))

and this in Z2:

=SUMPRODUCT(($B$2:$B$250=$X2)*($F$2:$F$250=1))

Note the similarity of the formulae - you can copy from Y2 into Z2 and
just amend the end part. Adjust the ranges from 250 if you have more
data than this.

Not sure why you told us that column H is status, as we haven't used
that.

Hope this helps.

Pete

On Feb 11, 5:16 pm, Jeremy <jeremiah.a.reynolds @ gmail.com wrote:
I have a table of information that I need to simplify into a summary table..

Column B = Name
Column F= Time
Column H = Status

I need to count all of the the items that have a time of greater than 24
hours and all of the times that have a time of less than 24 hours. *To
further complicate things, I need to count by individual names.

My Hope is to have the final table look something like this:

Billie (Under 24 Hours) * * [Number]
Billie (Over 24 Hours) * [Number]

Jeremy (Under 24 Hours) * * [Number]
Jeremy (Over 24 hours) * * [Number]
There will also be names that are not on the list. *I need those to return
Zero.

Please help.- Hide quoted text -

- Show quoted text -

Now it makes much more sense, and I think it works. Two final questions:

if I change it to
=SUMPRODUCT((($B$2:$B$300=$M8)*($F$2:$F$300<24)))* ($H$2:$H$300="Z9")

will that only count the items that have the Z9 Status in column H? There
are only 2 types of status in this report, Z9 and ZB. I do some other
manipulatin with the ZB orders. I only need to count the orders in Z9. You
will note that I changed your 1 to a 24. My data is in numbers.

Also, If make the range larger than the data, will this mess up the formula,
having empty cells?

"Pete_UK" wrote:

Did you try the formulae?

If your times are in Excel time format, then 24 hours equates to a
value of 1, as times are stored internally as fractions of a 24-hour
day. Thus, comparing column F values with 1 is the same as comparing
them with 24 hours, and the other comparison is checking on the names
in column B being the same as the name in X2. When the formula is
copied down this changes to X3, X4 etc, i.e. it will take in the
different names you have listed (once) in column X.

Try it and see.

Pete

On Feb 11, 5:46 pm, Jeremy <jeremiah.a.reynolds @ gmail.com wrote:
I see what you're doing here, but I don't think it will give me what I need.
I am looking for how many times does the Name 'Billie" appear in Column B
when The corresponding cell in Column F is (greater and less than(2
formulas)) 24 and the corresponding cell in column H is Z9, I don't need
information on the other status after a second look.



"Pete_UK" wrote:
Assume your summary table starts with names in X2 on the same sheet,
and that you have 250 rows of data starting on row 2. It might look
better if you used column Y for under 24 hours and column Z for
greater than 24 hours, so put this formula in Y2:

=SUMPRODUCT(($B$2:$B$250=$X2)*($F$2:$F$250<1))

and this in Z2:

=SUMPRODUCT(($B$2:$B$250=$X2)*($F$2:$F$250=1))

Note the similarity of the formulae - you can copy from Y2 into Z2 and
just amend the end part. Adjust the ranges from 250 if you have more
data than this.

Not sure why you told us that column H is status, as we haven't used
that.

Hope this helps.

Pete

On Feb 11, 5:16 pm, Jeremy <jeremiah.a.reynolds @ gmail.com wrote:
I have a table of information that I need to simplify into a summary table..

Column B = Name
Column F= Time
Column H = Status

I need to count all of the the items that have a time of greater than 24
hours and all of the times that have a time of less than 24 hours. To
further complicate things, I need to count by individual names.

My Hope is to have the final table look something like this:

Billie (Under 24 Hours) [Number]
Billie (Over 24 Hours) [Number]

Jeremy (Under 24 Hours) [Number]
Jeremy (Over 24 hours) [Number]
There will also be names that are not on the list. I need those to return
Zero.

Please help.- Hide quoted text -

- Show quoted text -

You will need to make it:

=SUMPRODUCT(($B$2:$B$300=$M8)*($F$2:$F$300<24)*($H $2:$H$300="Z9"))

Should be okay if you have less than 300 rows of data (in this case).

Hope this helps.

Pete

On Feb 11, 7:56*pm, Jeremy <jeremiah.a.reynolds @ gmail.com wrote:
Now it makes much more sense, and I think it works. *Two final questions:

if I change it to
=SUMPRODUCT((($B$2:$B$300=$M8)*($F$2:$F$300<24)))* ($H$2:$H$300="Z9")

will that only count the items that have the Z9 Status in column H? There
are only 2 types of status in this report, Z9 and ZB. *I do some other
manipulatin with the ZB orders. *I only need to count the orders in Z9. *You
will note that I changed your 1 to a 24. *My data is in numbers.

Also, If make the range larger than the data, will this mess up the formula,
having empty cells?



"Pete_UK" wrote:
Did you try the formulae?

If your times are in Excel time format, then 24 hours equates to a
value of 1, as times are stored internally as fractions of a 24-hour
day. Thus, comparing column F values with 1 is the same as comparing
them with 24 hours, and the other comparison is checking on the names
in column B being the same as the name in X2. When the formula is
copied down this changes to X3, X4 etc, i.e. it will take in the
different names you have listed (once) in column X.

Try it and see.

Pete

On Feb 11, 5:46 pm, Jeremy <jeremiah.a.reynolds @ gmail.com wrote:
I see what you're doing here, but I don't think it will give me what I need. *
I am looking for how many times does the Name 'Billie" appear in Column B
when The corresponding cell in Column F is (greater and less than(2
formulas)) 24 and the corresponding cell in column H is Z9, I don't need
information on the other status after a second look.

"Pete_UK" wrote:
Assume your summary table starts with names in X2 on the same sheet,
and that you have 250 rows of data starting on row 2. It might look
better if you used column Y for under 24 hours and column Z for
greater than 24 hours, so put this formula in Y2:

=SUMPRODUCT(($B$2:$B$250=$X2)*($F$2:$F$250<1))

and this in Z2:

=SUMPRODUCT(($B$2:$B$250=$X2)*($F$2:$F$250=1))

Note the similarity of the formulae - you can copy from Y2 into Z2 and
just amend the end part. Adjust the ranges from 250 if you have more
data than this.

Not sure why you told us that column H is status, as we haven't used
that.

Hope this helps.

Pete

On Feb 11, 5:16 pm, Jeremy <jeremiah.a.reynolds @ gmail.com wrote:
I have a table of information that I need to simplify into a summary table..

Column B = Name
Column F= Time
Column H = Status

I need to count all of the the items that have a time of greater than 24
hours and all of the times that have a time of less than 24 hours. *To
further complicate things, I need to count by individual names.

My Hope is to have the final table look something like this:

Billie (Under 24 Hours) * * [Number]
Billie (Over 24 Hours) * [Number]

Jeremy (Under 24 Hours) * * [Number]
Jeremy (Over 24 hours) * * [Number]
There will also be names that are not on the list. *I need those to return
Zero.

Please help.- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -