Using CountIf

[brucek]

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

 

[Peo Sjoblom]

=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]

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]

Try

=SUMPRODUCT(--(A2:A50="White"),--(B2:B50>=--"05:00"),--(B2:B50<--"10:00"))


Peo

 

[brucek]

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"),--(D211="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]

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]

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]

If you use a custom format [hh}:mm:ss
what do you see in the time columns?

 

[brucek]

I rechecked my formatting and it's now working - Thanks!

 

[brucek]

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?

If you use a custom format [hh}:mm:ss
what do you see in the time columns?



--

Regards,

Peo Sjoblom

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]

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

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?

If you use a custom format [hh}:mm:ss
what do you see in the time columns?



--

Regards,

Peo Sjoblom

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?

:

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



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"),--(D211="11"),--(E2:E11<--"5:00:00"))

The Count should be 6 but I get 0

Any thoughts?

My time is formated (h):hh:ss

:

Try

=SUMPRODUCT(--(A2:A50="White"),--(B2:B50>=--"05:00"),--(B2:B50<--"10:00"))


Peo

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?

:

=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


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