| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Correct Count
I'm trying to count the number of names that fall with in a certain alpha
range and numerical range. I can do one or the other but can't seem to do both. Let's say in column B, I have 20 names ranging from A to Zachary and in column E, I have numerical values ranging from 1 to 60. I want to count the number of names that fall between A & Jackson and 11 to 20. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Correct Count
Use cells to hold your criteria:
G1 = A H1 = Jackson I1 = 11 J1 = 20 =SUMPRODUCT(--(A1:A20=G1),--(A1:A20<=H1),--(E1:E20=I1),--(E1:E20<=J1)) -- Biff Microsoft Excel MVP "GAIDEN" wrote in message ... I'm trying to count the number of names that fall with in a certain alpha range and numerical range. I can do one or the other but can't seem to do both. Let's say in column B, I have 20 names ranging from A to Zachary and in column E, I have numerical values ranging from 1 to 60. I want to count the number of names that fall between A & Jackson and 11 to 20. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Correct Count
Hi,
Assuming the same layout as Biff, in 2007: =COUNTIFS(A1:A20,"="&G1,A1:A20,"<="&H1,E1:E20,"= "&I1,E1:E20,"<="&J1) -- If this helps, please click the Yes button. Cheers, Shane Devenshire "GAIDEN" wrote: I'm trying to count the number of names that fall with in a certain alpha range and numerical range. I can do one or the other but can't seem to do both. Let's say in column B, I have 20 names ranging from A to Zachary and in column E, I have numerical values ranging from 1 to 60. I want to count the number of names that fall between A & Jackson and 11 to 20. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Correct Count
That helped. Thank you
"T. Valko" wrote: Use cells to hold your criteria: G1 = A H1 = Jackson I1 = 11 J1 = 20 =SUMPRODUCT(--(A1:A20=G1),--(A1:A20<=H1),--(E1:E20=I1),--(E1:E20<=J1)) -- Biff Microsoft Excel MVP "GAIDEN" wrote in message ... I'm trying to count the number of names that fall with in a certain alpha range and numerical range. I can do one or the other but can't seem to do both. Let's say in column B, I have 20 names ranging from A to Zachary and in column E, I have numerical values ranging from 1 to 60. I want to count the number of names that fall between A & Jackson and 11 to 20. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Correct Count
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "GAIDEN" wrote in message ... That helped. Thank you "T. Valko" wrote: Use cells to hold your criteria: G1 = A H1 = Jackson I1 = 11 J1 = 20 =SUMPRODUCT(--(A1:A20=G1),--(A1:A20<=H1),--(E1:E20=I1),--(E1:E20<=J1)) -- Biff Microsoft Excel MVP "GAIDEN" wrote in message ... I'm trying to count the number of names that fall with in a certain alpha range and numerical range. I can do one or the other but can't seem to do both. Let's say in column B, I have 20 names ranging from A to Zachary and in column E, I have numerical values ranging from 1 to 60. I want to count the number of names that fall between A & Jackson and 11 to 20. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Correct Count
have another question for you. using the same info as before. if i added a
column with dollar amounts, how would i add the dollar amounts for the names that fall between A & Jackson and 11 to 20? "T. Valko" wrote: You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "GAIDEN" wrote in message ... That helped. Thank you "T. Valko" wrote: Use cells to hold your criteria: G1 = A H1 = Jackson I1 = 11 J1 = 20 =SUMPRODUCT(--(A1:A20=G1),--(A1:A20<=H1),--(E1:E20=I1),--(E1:E20<=J1)) -- Biff Microsoft Excel MVP "GAIDEN" wrote in message ... I'm trying to count the number of names that fall with in a certain alpha range and numerical range. I can do one or the other but can't seem to do both. Let's say in column B, I have 20 names ranging from A to Zachary and in column E, I have numerical values ranging from 1 to 60. I want to count the number of names that fall between A & Jackson and 11 to 20. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Correct Count
Let's assume the range to sum is F1:F20 -
=SUMPRODUCT(--(A1:A20=G1),--(A1:A20<=H1),--(E1:E20=I1),--(E1:E20<=J1),F1:F20) -- Biff Microsoft Excel MVP "GAIDEN" wrote in message ... have another question for you. using the same info as before. if i added a column with dollar amounts, how would i add the dollar amounts for the names that fall between A & Jackson and 11 to 20? "T. Valko" wrote: You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "GAIDEN" wrote in message ... That helped. Thank you "T. Valko" wrote: Use cells to hold your criteria: G1 = A H1 = Jackson I1 = 11 J1 = 20 =SUMPRODUCT(--(A1:A20=G1),--(A1:A20<=H1),--(E1:E20=I1),--(E1:E20<=J1)) -- Biff Microsoft Excel MVP "GAIDEN" wrote in message ... I'm trying to count the number of names that fall with in a certain alpha range and numerical range. I can do one or the other but can't seem to do both. Let's say in column B, I have 20 names ranging from A to Zachary and in column E, I have numerical values ranging from 1 to 60. I want to count the number of names that fall between A & Jackson and 11 to 20. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Correct Count
Thanks again. It worked.
"T. Valko" wrote: Let's assume the range to sum is F1:F20 - =SUMPRODUCT(--(A1:A20=G1),--(A1:A20<=H1),--(E1:E20=I1),--(E1:E20<=J1),F1:F20) -- Biff Microsoft Excel MVP "GAIDEN" wrote in message ... have another question for you. using the same info as before. if i added a column with dollar amounts, how would i add the dollar amounts for the names that fall between A & Jackson and 11 to 20? "T. Valko" wrote: You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "GAIDEN" wrote in message ... That helped. Thank you "T. Valko" wrote: Use cells to hold your criteria: G1 = A H1 = Jackson I1 = 11 J1 = 20 =SUMPRODUCT(--(A1:A20=G1),--(A1:A20<=H1),--(E1:E20=I1),--(E1:E20<=J1)) -- Biff Microsoft Excel MVP "GAIDEN" wrote in message ... I'm trying to count the number of names that fall with in a certain alpha range and numerical range. I can do one or the other but can't seem to do both. Let's say in column B, I have 20 names ranging from A to Zachary and in column E, I have numerical values ranging from 1 to 60. I want to count the number of names that fall between A & Jackson and 11 to 20. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Correct Count
You're welcome!
-- Biff Microsoft Excel MVP "GAIDEN" wrote in message ... Thanks again. It worked. "T. Valko" wrote: Let's assume the range to sum is F1:F20 - =SUMPRODUCT(--(A1:A20=G1),--(A1:A20<=H1),--(E1:E20=I1),--(E1:E20<=J1),F1:F20) -- Biff Microsoft Excel MVP "GAIDEN" wrote in message ... have another question for you. using the same info as before. if i added a column with dollar amounts, how would i add the dollar amounts for the names that fall between A & Jackson and 11 to 20? "T. Valko" wrote: You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "GAIDEN" wrote in message ... That helped. Thank you "T. Valko" wrote: Use cells to hold your criteria: G1 = A H1 = Jackson I1 = 11 J1 = 20 =SUMPRODUCT(--(A1:A20=G1),--(A1:A20<=H1),--(E1:E20=I1),--(E1:E20<=J1)) -- Biff Microsoft Excel MVP "GAIDEN" wrote in message ... I'm trying to count the number of names that fall with in a certain alpha range and numerical range. I can do one or the other but can't seem to do both. Let's say in column B, I have 20 names ranging from A to Zachary and in column E, I have numerical values ranging from 1 to 60. I want to count the number of names that fall between A & Jackson and 11 to 20. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Correct #N/A Value | Excel Worksheet Functions | |||
| Formula required to get the correct count. | Excel Discussion (Misc queries) | |||
| Formula required to get the correct count. | Excel Discussion (Misc queries) | |||
| Not sure if count is the correct formula... Excel 2002 | Excel Worksheet Functions | |||
| If with AND - Is this correct? | Excel Worksheet Functions | |||
Linear Mode
