I have a spreadsheet with Ethnicity (White, African American, hispanic,
Asian, other) in column A and Time (hh:mm:ss) in column B.
I trying to determine by how many White, for example, have spent less than 5
hours, >5 hrs but less than 10 hr, etc.
My plan is to make a chart of the data.
I've been looking at sumproduct and COUNTIF but I can't quite figure it out.
Any help is greatly appreciated
=SUMPRODUCT(--(A2:A50="White"),--(B2:B50>--"05:00"),--(B2:B50<--"10:00"))
if you replace all the criteria with cell refs then you can use
=SUMPRODUCT(--(A2:A50=C2),--(B2:B50>D2),--(B2:B50<E2))
Regards,
Peo Sjoblom
"brucek" <brucek@discussions.microsoft.com> wrote in message
news:A4C2A045-AB16-4D7F-9A9B-9B3157ADAD0A@microsoft.com...
>I have a spreadsheet with Ethnicity (White, African American, hispanic,
> Asian, other) in column A and Time (hh:mm:ss) in column B.
>
> I trying to determine by how many White, for example, have spent less than
> 5
> hours, >5 hrs but less than 10 hr, etc.
>
> My plan is to make a chart of the data.
> I've been looking at sumproduct and COUNTIF but I can't quite figure it
> out.
>
> Any help is greatly appreciated
Thanks for the quick response.
I just noticed that I wrote my question for time range incorrectly. It
should have read "greater than or equal to 5 hours but less than 10 hours"
does this make for a 3rd argument?
"Peo Sjoblom" wrote:
> =SUMPRODUCT(--(A2:A50="White"),--(B2:B50>--"05:00"),--(B2:B50<--"10:00"))
>
> if you replace all the criteria with cell refs then you can use
>
> =SUMPRODUCT(--(A2:A50=C2),--(B2:B50>D2),--(B2:B50<E2))
>
> Regards,
>
> Peo Sjoblom
>
>
> "brucek" <brucek@discussions.microsoft.com> wrote in message
> news:A4C2A045-AB16-4D7F-9A9B-9B3157ADAD0A@microsoft.com...
> >I have a spreadsheet with Ethnicity (White, African American, hispanic,
> > Asian, other) in column A and Time (hh:mm:ss) in column B.
> >
> > I trying to determine by how many White, for example, have spent less than
> > 5
> > hours, >5 hrs but less than 10 hr, etc.
> >
> > My plan is to make a chart of the data.
> > I've been looking at sumproduct and COUNTIF but I can't quite figure it
> > out.
> >
> > Any help is greatly appreciated
>
>
>
Try
=SUMPRODUCT(--(A2:A50="White"),--(B2:B50>=--"05:00"),--(B2:B50<--"10:00"))
Peo
"brucek" <brucek@discussions.microsoft.com> wrote in message
news:FE8BE8C3-C731-46ED-9964-7AA2F06590DD@microsoft.com...
> Thanks for the quick response.
>
> I just noticed that I wrote my question for time range incorrectly. It
> should have read "greater than or equal to 5 hours but less than 10 hours"
> does this make for a 3rd argument?
>
> "Peo Sjoblom" wrote:
>
>> =SUMPRODUCT(--(A2:A50="White"),--(B2:B50>--"05:00"),--(B2:B50<--"10:00"))
>>
>> if you replace all the criteria with cell refs then you can use
>>
>> =SUMPRODUCT(--(A2:A50=C2),--(B2:B50>D2),--(B2:B50<E2))
>>
>> Regards,
>>
>> Peo Sjoblom
>>
>>
>> "brucek" <brucek@discussions.microsoft.com> wrote in message
>> news:A4C2A045-AB16-4D7F-9A9B-9B3157ADAD0A@microsoft.com...
>> >I have a spreadsheet with Ethnicity (White, African American, hispanic,
>> > Asian, other) in column A and Time (hh:mm:ss) in column B.
>> >
>> > I trying to determine by how many White, for example, have spent less
>> > than
>> > 5
>> > hours, >5 hrs but less than 10 hr, etc.
>> >
>> > My plan is to make a chart of the data.
>> > I've been looking at sumproduct and COUNTIF but I can't quite figure it
>> > out.
>> >
>> > Any help is greatly appreciated
>>
>>
>>
I've tried your formula but I'm not getting the right count
Her's an extract from the spreadsheet
A B C D E
Name Ethnicity Gender Grade Time
, Black Female 12 17:35:43
, Black Female 11 0:00:36
, Black Male 11 7:02:13
, Black Female 11 0:54:27
, Black Male 11 1:22:18
, Black Male 11 0:00:00
, Black Male 11 0:00:00
, Black Female 11 0:00:00
, Black Male 12 0:00:00
, Black Male 12 0:00:00
Trying to track the number of Black 11th graders with less than five hours I
uused the following formula:
=SUMPRODUCT(--(B2:B11="Black"),--(D2:D11="11"),--(E2:E11<--"5:00:00"))
The Count should be 6 but I get 0
Any thoughts?
My time is formated (h):hh:ss
"Peo Sjoblom" wrote:
> Try
>
> =SUMPRODUCT(--(A2:A50="White"),--(B2:B50>=--"05:00"),--(B2:B50<--"10:00"))
>
>
> Peo
>
> "brucek" <brucek@discussions.microsoft.com> wrote in message
> news:FE8BE8C3-C731-46ED-9964-7AA2F06590DD@microsoft.com...
> > Thanks for the quick response.
> >
> > I just noticed that I wrote my question for time range incorrectly. It
> > should have read "greater than or equal to 5 hours but less than 10 hours"
> > does this make for a 3rd argument?
> >
> > "Peo Sjoblom" wrote:
> >
> >> =SUMPRODUCT(--(A2:A50="White"),--(B2:B50>--"05:00"),--(B2:B50<--"10:00"))
> >>
> >> if you replace all the criteria with cell refs then you can use
> >>
> >> =SUMPRODUCT(--(A2:A50=C2),--(B2:B50>D2),--(B2:B50<E2))
> >>
> >> Regards,
> >>
> >> Peo Sjoblom
> >>
> >>
> >> "brucek" <brucek@discussions.microsoft.com> wrote in message
> >> news:A4C2A045-AB16-4D7F-9A9B-9B3157ADAD0A@microsoft.com...
> >> >I have a spreadsheet with Ethnicity (White, African American, hispanic,
> >> > Asian, other) in column A and Time (hh:mm:ss) in column B.
> >> >
> >> > I trying to determine by how many White, for example, have spent less
> >> > than
> >> > 5
> >> > hours, >5 hrs but less than 10 hr, etc.
> >> >
> >> > My plan is to make a chart of the data.
> >> > I've been looking at sumproduct and COUNTIF but I can't quite figure it
> >> > out.
> >> >
> >> > Any help is greatly appreciated
> >>
> >>
> >>
>
>
>
I can only assume that either you have hidden spaces in the text or that
your time is text,
test it by typing in a little test table
--
Regards,
Peo Sjoblom
"brucek" <brucek@discussions.microsoft.com> wrote in message
news:9BA0603D-BF9D-4419-A79C-0C4B533A8C39@microsoft.com...
> I've tried your formula but I'm not getting the right count
>
> Her's an extract from the spreadsheet
> A B C D E
> Name Ethnicity Gender Grade Time
> , Black Female 12 17:35:43
> , Black Female 11 0:00:36
> , Black Male 11 7:02:13
> , Black Female 11 0:54:27
> , Black Male 11 1:22:18
> , Black Male 11 0:00:00
> , Black Male 11 0:00:00
> , Black Female 11 0:00:00
> , Black Male 12 0:00:00
> , Black Male 12 0:00:00
>
> Trying to track the number of Black 11th graders with less than five hours
> I
> uused the following formula:
> =SUMPRODUCT(--(B2:B11="Black"),--(D2:D11="11"),--(E2:E11<--"5:00:00"))
>
> The Count should be 6 but I get 0
>
> Any thoughts?
>
> My time is formated (h):hh:ss
>
> "Peo Sjoblom" wrote:
>
>> Try
>>
>> =SUMPRODUCT(--(A2:A50="White"),--(B2:B50>=--"05:00"),--(B2:B50<--"10:00"))
>>
>>
>> Peo
>>
>> "brucek" <brucek@discussions.microsoft.com> wrote in message
>> news:FE8BE8C3-C731-46ED-9964-7AA2F06590DD@microsoft.com...
>> > Thanks for the quick response.
>> >
>> > I just noticed that I wrote my question for time range incorrectly. It
>> > should have read "greater than or equal to 5 hours but less than 10
>> > hours"
>> > does this make for a 3rd argument?
>> >
>> > "Peo Sjoblom" wrote:
>> >
>> >> =SUMPRODUCT(--(A2:A50="White"),--(B2:B50>--"05:00"),--(B2:B50<--"10:00"))
>> >>
>> >> if you replace all the criteria with cell refs then you can use
>> >>
>> >> =SUMPRODUCT(--(A2:A50=C2),--(B2:B50>D2),--(B2:B50<E2))
>> >>
>> >> Regards,
>> >>
>> >> Peo Sjoblom
>> >>
>> >>
>> >> "brucek" <brucek@discussions.microsoft.com> wrote in message
>> >> news:A4C2A045-AB16-4D7F-9A9B-9B3157ADAD0A@microsoft.com...
>> >> >I have a spreadsheet with Ethnicity (White, African American,
>> >> >hispanic,
>> >> > Asian, other) in column A and Time (hh:mm:ss) in column B.
>> >> >
>> >> > I trying to determine by how many White, for example, have spent
>> >> > less
>> >> > than
>> >> > 5
>> >> > hours, >5 hrs but less than 10 hr, etc.
>> >> >
>> >> > My plan is to make a chart of the data.
>> >> > I've been looking at sumproduct and COUNTIF but I can't quite figure
>> >> > it
>> >> > out.
>> >> >
>> >> > Any help is greatly appreciated
>> >>
>> >>
>> >>
>>
>>
>>
I use a formula to calculate the total time by summng 2 different columns.
the value displayed is the time but when I click on the cell it displays the
formula. Could this be the problem?
"Peo Sjoblom" wrote:
> I can only assume that either you have hidden spaces in the text or that
> your time is text,
> test it by typing in a little test table
>
>
>
> --
>
> Regards,
>
> Peo Sjoblom
>
>
>
> "brucek" <brucek@discussions.microsoft.com> wrote in message
> news:9BA0603D-BF9D-4419-A79C-0C4B533A8C39@microsoft.com...
> > I've tried your formula but I'm not getting the right count
> >
> > Her's an extract from the spreadsheet
> > A B C D E
> > Name Ethnicity Gender Grade Time
> > , Black Female 12 17:35:43
> > , Black Female 11 0:00:36
> > , Black Male 11 7:02:13
> > , Black Female 11 0:54:27
> > , Black Male 11 1:22:18
> > , Black Male 11 0:00:00
> > , Black Male 11 0:00:00
> > , Black Female 11 0:00:00
> > , Black Male 12 0:00:00
> > , Black Male 12 0:00:00
> >
> > Trying to track the number of Black 11th graders with less than five hours
> > I
> > uused the following formula:
> > =SUMPRODUCT(--(B2:B11="Black"),--(D2:D11="11"),--(E2:E11<--"5:00:00"))
> >
> > The Count should be 6 but I get 0
> >
> > Any thoughts?
> >
> > My time is formated (h):hh:ss
> >
> > "Peo Sjoblom" wrote:
> >
> >> Try
> >>
> >> =SUMPRODUCT(--(A2:A50="White"),--(B2:B50>=--"05:00"),--(B2:B50<--"10:00"))
> >>
> >>
> >> Peo
> >>
> >> "brucek" <brucek@discussions.microsoft.com> wrote in message
> >> news:FE8BE8C3-C731-46ED-9964-7AA2F06590DD@microsoft.com...
> >> > Thanks for the quick response.
> >> >
> >> > I just noticed that I wrote my question for time range incorrectly. It
> >> > should have read "greater than or equal to 5 hours but less than 10
> >> > hours"
> >> > does this make for a 3rd argument?
> >> >
> >> > "Peo Sjoblom" wrote:
> >> >
> >> >> =SUMPRODUCT(--(A2:A50="White"),--(B2:B50>--"05:00"),--(B2:B50<--"10:00"))
> >> >>
> >> >> if you replace all the criteria with cell refs then you can use
> >> >>
> >> >> =SUMPRODUCT(--(A2:A50=C2),--(B2:B50>D2),--(B2:B50<E2))
> >> >>
> >> >> Regards,
> >> >>
> >> >> Peo Sjoblom
> >> >>
> >> >>
> >> >> "brucek" <brucek@discussions.microsoft.com> wrote in message
> >> >> news:A4C2A045-AB16-4D7F-9A9B-9B3157ADAD0A@microsoft.com...
> >> >> >I have a spreadsheet with Ethnicity (White, African American,
> >> >> >hispanic,
> >> >> > Asian, other) in column A and Time (hh:mm:ss) in column B.
> >> >> >
> >> >> > I trying to determine by how many White, for example, have spent
> >> >> > less
> >> >> > than
> >> >> > 5
> >> >> > hours, >5 hrs but less than 10 hr, etc.
> >> >> >
> >> >> > My plan is to make a chart of the data.
> >> >> > I've been looking at sumproduct and COUNTIF but I can't quite figure
> >> >> > it
> >> >> > out.
> >> >> >
> >> >> > Any help is greatly appreciated
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>
If you use a custom format [hh}:mm:ss
what do you see in the time columns?
--
Regards,
Peo Sjoblom
"brucek" <brucek@discussions.microsoft.com> wrote in message
news:60B3BB67-4F35-41F9-A62D-65351212D31A@microsoft.com...
>I use a formula to calculate the total time by summng 2 different columns.
> the value displayed is the time but when I click on the cell it displays
> the
> formula. Could this be the problem?
>
> "Peo Sjoblom" wrote:
>
>> I can only assume that either you have hidden spaces in the text or that
>> your time is text,
>> test it by typing in a little test table
>>
>>
>>
>> --
>>
>> Regards,
>>
>> Peo Sjoblom
>>
>>
>>
>> "brucek" <brucek@discussions.microsoft.com> wrote in message
>> news:9BA0603D-BF9D-4419-A79C-0C4B533A8C39@microsoft.com...
>> > I've tried your formula but I'm not getting the right count
>> >
>> > Her's an extract from the spreadsheet
>> > A B C D E
>> > Name Ethnicity Gender Grade Time
>> > , Black Female 12 17:35:43
>> > , Black Female 11 0:00:36
>> > , Black Male 11 7:02:13
>> > , Black Female 11 0:54:27
>> > , Black Male 11 1:22:18
>> > , Black Male 11 0:00:00
>> > , Black Male 11 0:00:00
>> > , Black Female 11 0:00:00
>> > , Black Male 12 0:00:00
>> > , Black Male 12 0:00:00
>> >
>> > Trying to track the number of Black 11th graders with less than five
>> > hours
>> > I
>> > uused the following formula:
>> > =SUMPRODUCT(--(B2:B11="Black"),--(D2:D11="11"),--(E2:E11<--"5:00:00"))
>> >
>> > The Count should be 6 but I get 0
>> >
>> > Any thoughts?
>> >
>> > My time is formated (h):hh:ss
>> >
>> > "Peo Sjoblom" wrote:
>> >
>> >> Try
>> >>
>> >> =SUMPRODUCT(--(A2:A50="White"),--(B2:B50>=--"05:00"),--(B2:B50<--"10:00"))
>> >>
>> >>
>> >> Peo
>> >>
>> >> "brucek" <brucek@discussions.microsoft.com> wrote in message
>> >> news:FE8BE8C3-C731-46ED-9964-7AA2F06590DD@microsoft.com...
>> >> > Thanks for the quick response.
>> >> >
>> >> > I just noticed that I wrote my question for time range incorrectly.
>> >> > It
>> >> > should have read "greater than or equal to 5 hours but less than 10
>> >> > hours"
>> >> > does this make for a 3rd argument?
>> >> >
>> >> > "Peo Sjoblom" wrote:
>> >> >
>> >> >> =SUMPRODUCT(--(A2:A50="White"),--(B2:B50>--"05:00"),--(B2:B50<--"10:00"))
>> >> >>
>> >> >> if you replace all the criteria with cell refs then you can use
>> >> >>
>> >> >> =SUMPRODUCT(--(A2:A50=C2),--(B2:B50>D2),--(B2:B50<E2))
>> >> >>
>> >> >> Regards,
>> >> >>
>> >> >> Peo Sjoblom
>> >> >>
>> >> >>
>> >> >> "brucek" <brucek@discussions.microsoft.com> wrote in message
>> >> >> news:A4C2A045-AB16-4D7F-9A9B-9B3157ADAD0A@microsoft.com...
>> >> >> >I have a spreadsheet with Ethnicity (White, African American,
>> >> >> >hispanic,
>> >> >> > Asian, other) in column A and Time (hh:mm:ss) in column B.
>> >> >> >
>> >> >> > I trying to determine by how many White, for example, have spent
>> >> >> > less
>> >> >> > than
>> >> >> > 5
>> >> >> > hours, >5 hrs but less than 10 hr, etc.
>> >> >> >
>> >> >> > My plan is to make a chart of the data.
>> >> >> > I've been looking at sumproduct and COUNTIF but I can't quite
>> >> >> > figure
>> >> >> > it
>> >> >> > out.
>> >> >> >
>> >> >> > Any help is greatly appreciated
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>
I rechecked my formatting and it's now working - Thanks!
"Peo Sjoblom" wrote:
> I can only assume that either you have hidden spaces in the text or that
> your time is text,
> test it by typing in a little test table
>
>
>
> --
>
> Regards,
>
> Peo Sjoblom
>
>
>
> "brucek" <brucek@discussions.microsoft.com> wrote in message
> news:9BA0603D-BF9D-4419-A79C-0C4B533A8C39@microsoft.com...
> > I've tried your formula but I'm not getting the right count
> >
> > Her's an extract from the spreadsheet
> > A B C D E
> > Name Ethnicity Gender Grade Time
> > , Black Female 12 17:35:43
> > , Black Female 11 0:00:36
> > , Black Male 11 7:02:13
> > , Black Female 11 0:54:27
> > , Black Male 11 1:22:18
> > , Black Male 11 0:00:00
> > , Black Male 11 0:00:00
> > , Black Female 11 0:00:00
> > , Black Male 12 0:00:00
> > , Black Male 12 0:00:00
> >
> > Trying to track the number of Black 11th graders with less than five hours
> > I
> > uused the following formula:
> > =SUMPRODUCT(--(B2:B11="Black"),--(D2:D11="11"),--(E2:E11<--"5:00:00"))
> >
> > The Count should be 6 but I get 0
> >
> > Any thoughts?
> >
> > My time is formated (h):hh:ss
> >
> > "Peo Sjoblom" wrote:
> >
> >> Try
> >>
> >> =SUMPRODUCT(--(A2:A50="White"),--(B2:B50>=--"05:00"),--(B2:B50<--"10:00"))
> >>
> >>
> >> Peo
> >>
> >> "brucek" <brucek@discussions.microsoft.com> wrote in message
> >> news:FE8BE8C3-C731-46ED-9964-7AA2F06590DD@microsoft.com...
> >> > Thanks for the quick response.
> >> >
> >> > I just noticed that I wrote my question for time range incorrectly. It
> >> > should have read "greater than or equal to 5 hours but less than 10
> >> > hours"
> >> > does this make for a 3rd argument?
> >> >
> >> > "Peo Sjoblom" wrote:
> >> >
> >> >> =SUMPRODUCT(--(A2:A50="White"),--(B2:B50>--"05:00"),--(B2:B50<--"10:00"))
> >> >>
> >> >> if you replace all the criteria with cell refs then you can use
> >> >>
> >> >> =SUMPRODUCT(--(A2:A50=C2),--(B2:B50>D2),--(B2:B50<E2))
> >> >>
> >> >> Regards,
> >> >>
> >> >> Peo Sjoblom
> >> >>
> >> >>
> >> >> "brucek" <brucek@discussions.microsoft.com> wrote in message
> >> >> news:A4C2A045-AB16-4D7F-9A9B-9B3157ADAD0A@microsoft.com...
> >> >> >I have a spreadsheet with Ethnicity (White, African American,
> >> >> >hispanic,
> >> >> > Asian, other) in column A and Time (hh:mm:ss) in column B.
> >> >> >
> >> >> > I trying to determine by how many White, for example, have spent
> >> >> > less
> >> >> > than
> >> >> > 5
> >> >> > hours, >5 hrs but less than 10 hr, etc.
> >> >> >
> >> >> > My plan is to make a chart of the data.
> >> >> > I've been looking at sumproduct and COUNTIF but I can't quite figure
> >> >> > it
> >> >> > out.
> >> >> >
> >> >> > Any help is greatly appreciated
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>
I see 10:14:12
I double checked my formatting and everything is working fine.
My next challenge is to copy the formula into the 17 different worksheets
within the workbook. All the columns are formatted the same but they have
varying numbers of rows. Any suggestions?
"Peo Sjoblom" wrote:
> If you use a custom format [hh}:mm:ss
> what do you see in the time columns?
>
>
>
> --
>
> Regards,
>
> Peo Sjoblom
>
>
> "brucek" <brucek@discussions.microsoft.com> wrote in message
> news:60B3BB67-4F35-41F9-A62D-65351212D31A@microsoft.com...
> >I use a formula to calculate the total time by summng 2 different columns.
> > the value displayed is the time but when I click on the cell it displays
> > the
> > formula. Could this be the problem?
> >
> > "Peo Sjoblom" wrote:
> >
> >> I can only assume that either you have hidden spaces in the text or that
> >> your time is text,
> >> test it by typing in a little test table
> >>
> >>
> >>
> >> --
> >>
> >> Regards,
> >>
> >> Peo Sjoblom
> >>
> >>
> >>
> >> "brucek" <brucek@discussions.microsoft.com> wrote in message
> >> news:9BA0603D-BF9D-4419-A79C-0C4B533A8C39@microsoft.com...
> >> > I've tried your formula but I'm not getting the right count
> >> >
> >> > Her's an extract from the spreadsheet
> >> > A B C D E
> >> > Name Ethnicity Gender Grade Time
> >> > , Black Female 12 17:35:43
> >> > , Black Female 11 0:00:36
> >> > , Black Male 11 7:02:13
> >> > , Black Female 11 0:54:27
> >> > , Black Male 11 1:22:18
> >> > , Black Male 11 0:00:00
> >> > , Black Male 11 0:00:00
> >> > , Black Female 11 0:00:00
> >> > , Black Male 12 0:00:00
> >> > , Black Male 12 0:00:00
> >> >
> >> > Trying to track the number of Black 11th graders with less than five
> >> > hours
> >> > I
> >> > uused the following formula:
> >> > =SUMPRODUCT(--(B2:B11="Black"),--(D2:D11="11"),--(E2:E11<--"5:00:00"))
> >> >
> >> > The Count should be 6 but I get 0
> >> >
> >> > Any thoughts?
> >> >
> >> > My time is formated (h):hh:ss
> >> >
> >> > "Peo Sjoblom" wrote:
> >> >
> >> >> Try
> >> >>
> >> >> =SUMPRODUCT(--(A2:A50="White"),--(B2:B50>=--"05:00"),--(B2:B50<--"10:00"))
> >> >>
> >> >>
> >> >> Peo
> >> >>
> >> >> "brucek" <brucek@discussions.microsoft.com> wrote in message
> >> >> news:FE8BE8C3-C731-46ED-9964-7AA2F06590DD@microsoft.com...
> >> >> > Thanks for the quick response.
> >> >> >
> >> >> > I just noticed that I wrote my question for time range incorrectly.
> >> >> > It
> >> >> > should have read "greater than or equal to 5 hours but less than 10
> >> >> > hours"
> >> >> > does this make for a 3rd argument?
> >> >> >
> >> >> > "Peo Sjoblom" wrote:
> >> >> >
> >> >> >> =SUMPRODUCT(--(A2:A50="White"),--(B2:B50>--"05:00"),--(B2:B50<--"10:00"))
> >> >> >>
> >> >> >> if you replace all the criteria with cell refs then you can use
> >> >> >>
> >> >> >> =SUMPRODUCT(--(A2:A50=C2),--(B2:B50>D2),--(B2:B50<E2))
> >> >> >>
> >> >> >> Regards,
> >> >> >>
> >> >> >> Peo Sjoblom
> >> >> >>
> >> >> >>
> >> >> >> "brucek" <brucek@discussions.microsoft.com> wrote in message
> >> >> >> news:A4C2A045-AB16-4D7F-9A9B-9B3157ADAD0A@microsoft.com...
> >> >> >> >I have a spreadsheet with Ethnicity (White, African American,
> >> >> >> >hispanic,
> >> >> >> > Asian, other) in column A and Time (hh:mm:ss) in column B.
> >> >> >> >
> >> >> >> > I trying to determine by how many White, for example, have spent
> >> >> >> > less
> >> >> >> > than
> >> >> >> > 5
> >> >> >> > hours, >5 hrs but less than 10 hr, etc.
> >> >> >> >
> >> >> >> > My plan is to make a chart of the data.
> >> >> >> > I've been looking at sumproduct and COUNTIF but I can't quite
> >> >> >> > figure
> >> >> >> > it
> >> >> >> > out.
> >> >> >> >
> >> >> >> > Any help is greatly appreciated
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>
Find the sheet with the most rows and use that range for all of them, put
the formulas in one sheet
select all sheets (click first and hold down shift and click last) then put
the formula in one sheet, that should do it in all sheets, right click one
sheet tab and select ungroup
--
Regards,
Peo Sjoblom
"brucek" <brucek@discussions.microsoft.com> wrote in message
news:557F0642-AE4B-49A2-834C-C3E144E2E37D@microsoft.com...
>I see 10:14:12
>
> I double checked my formatting and everything is working fine.
>
> My next challenge is to copy the formula into the 17 different worksheets
> within the workbook. All the columns are formatted the same but they have
> varying numbers of rows. Any suggestions?
>
> "Peo Sjoblom" wrote:
>
>> If you use a custom format [hh}:mm:ss
>> what do you see in the time columns?
>>
>>
>>
>> --
>>
>> Regards,
>>
>> Peo Sjoblom
>>
>>
>> "brucek" <brucek@discussions.microsoft.com> wrote in message
>> news:60B3BB67-4F35-41F9-A62D-65351212D31A@microsoft.com...
>> >I use a formula to calculate the total time by summng 2 different
>> >columns.
>> > the value displayed is the time but when I click on the cell it
>> > displays
>> > the
>> > formula. Could this be the problem?
>> >
>> > "Peo Sjoblom" wrote:
>> >
>> >> I can only assume that either you have hidden spaces in the text or
>> >> that
>> >> your time is text,
>> >> test it by typing in a little test table
>> >>
>> >>
>> >>
>> >> --
>> >>
>> >> Regards,
>> >>
>> >> Peo Sjoblom
>> >>
>> >>
>> >>
>> >> "brucek" <brucek@discussions.microsoft.com> wrote in message
>> >> news:9BA0603D-BF9D-4419-A79C-0C4B533A8C39@microsoft.com...
>> >> > I've tried your formula but I'm not getting the right count
>> >> >
>> >> > Her's an extract from the spreadsheet
>> >> > A B C D E
>> >> > Name Ethnicity Gender Grade Time
>> >> > , Black Female 12 17:35:43
>> >> > , Black Female 11 0:00:36
>> >> > , Black Male 11 7:02:13
>> >> > , Black Female 11 0:54:27
>> >> > , Black Male 11 1:22:18
>> >> > , Black Male 11 0:00:00
>> >> > , Black Male 11 0:00:00
>> >> > , Black Female 11 0:00:00
>> >> > , Black Male 12 0:00:00
>> >> > , Black Male 12 0:00:00
>> >> >
>> >> > Trying to track the number of Black 11th graders with less than five
>> >> > hours
>> >> > I
>> >> > uused the following formula:
>> >> > =SUMPRODUCT(--(B2:B11="Black"),--(D2:D11="11"),--(E2:E11<--"5:00:00"))
>> >> >
>> >> > The Count should be 6 but I get 0
>> >> >
>> >> > Any thoughts?
>> >> >
>> >> > My time is formated (h):hh:ss
>> >> >
>> >> > "Peo Sjoblom" wrote:
>> >> >
>> >> >> Try
>> >> >>
>> >> >> =SUMPRODUCT(--(A2:A50="White"),--(B2:B50>=--"05:00"),--(B2:B50<--"10:00"))
>> >> >>
>> >> >>
>> >> >> Peo
>> >> >>
>> >> >> "brucek" <brucek@discussions.microsoft.com> wrote in message
>> >> >> news:FE8BE8C3-C731-46ED-9964-7AA2F06590DD@microsoft.com...
>> >> >> > Thanks for the quick response.
>> >> >> >
>> >> >> > I just noticed that I wrote my question for time range
>> >> >> > incorrectly.
>> >> >> > It
>> >> >> > should have read "greater than or equal to 5 hours but less than
>> >> >> > 10
>> >> >> > hours"
>> >> >> > does this make for a 3rd argument?
>> >> >> >
>> >> >> > "Peo Sjoblom" wrote:
>> >> >> >
>> >> >> >> =SUMPRODUCT(--(A2:A50="White"),--(B2:B50>--"05:00"),--(B2:B50<--"10:00"))
>> >> >> >>
>> >> >> >> if you replace all the criteria with cell refs then you can use
>> >> >> >>
>> >> >> >> =SUMPRODUCT(--(A2:A50=C2),--(B2:B50>D2),--(B2:B50<E2))
>> >> >> >>
>> >> >> >> Regards,
>> >> >> >>
>> >> >> >> Peo Sjoblom
>> >> >> >>
>> >> >> >>
>> >> >> >> "brucek" <brucek@discussions.microsoft.com> wrote in message
>> >> >> >> news:A4C2A045-AB16-4D7F-9A9B-9B3157ADAD0A@microsoft.com...
>> >> >> >> >I have a spreadsheet with Ethnicity (White, African American,
>> >> >> >> >hispanic,
>> >> >> >> > Asian, other) in column A and Time (hh:mm:ss) in column B.
>> >> >> >> >
>> >> >> >> > I trying to determine by how many White, for example, have
>> >> >> >> > spent
>> >> >> >> > less
>> >> >> >> > than
>> >> >> >> > 5
>> >> >> >> > hours, >5 hrs but less than 10 hr, etc.
>> >> >> >> >
>> >> >> >> > My plan is to make a chart of the data.
>> >> >> >> > I've been looking at sumproduct and COUNTIF but I can't quite
>> >> >> >> > figure
>> >> >> >> > it
>> >> >> >> > out.
>> >> >> >> >
>> >> >> >> > Any help is greatly appreciated
>> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks