|
Using CountIf
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 |
Using CountIf
=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:B50D2),--(B2:B50<E2)) Regards, Peo Sjoblom "brucek" wrote in message ... 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 |
Using CountIf
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:B50D2),--(B2:B50<E2)) Regards, Peo Sjoblom "brucek" wrote in message ... 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 |
Using CountIf
Try
=SUMPRODUCT(--(A2:A50="White"),--(B2:B50=--"05:00"),--(B2:B50<--"10:00")) Peo "brucek" wrote in message ... 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:B50D2),--(B2:B50<E2)) Regards, Peo Sjoblom "brucek" wrote in message ... 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 |
Using CountIf
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" wrote in message ... 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:B50D2),--(B2:B50<E2)) Regards, Peo Sjoblom "brucek" wrote in message ... 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 |
Using CountIf
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" wrote in message ... 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" wrote in message ... 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:B50D2),--(B2:B50<E2)) Regards, Peo Sjoblom "brucek" wrote in message ... 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 |
Using CountIf
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" wrote in message ... 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" wrote in message ... 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:B50D2),--(B2:B50<E2)) Regards, Peo Sjoblom "brucek" wrote in message ... 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 |
Using CountIf
If you use a custom format [hh}:mm:ss
what do you see in the time columns? -- Regards, Peo Sjoblom "brucek" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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:B50D2),--(B2:B50<E2)) Regards, Peo Sjoblom "brucek" wrote in message ... 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 |
Using CountIf
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" wrote in message ... 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" wrote in message ... 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:B50D2),--(B2:B50<E2)) Regards, Peo Sjoblom "brucek" wrote in message ... 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 |
Using CountIf
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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:B50D2),--(B2:B50<E2)) Regards, Peo Sjoblom "brucek" wrote in message ... 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 |
Using CountIf
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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:B50D2),--(B2:B50<E2)) Regards, Peo Sjoblom "brucek" wrote in message ... 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 |
| All times are GMT +1. The time now is 06:23 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com