| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count Specific Item in Specific Row
I'm a newbie and sorry about the nomenclature. I want to be able to count specific entries in Columns B and C against a specific criteria in Column A. Let's say the sheet has 100 rows of three columns (A,B,C). Column A rows will have entered 1, 2, or 3. Columns B and C will have entered W, X, or Y. So we can have 1 W W, 2 W X, 1 X X, 3 Y Y, etc. What I want to be able to do is to determine how many W X and Y EACH are in the rows with only 1, only 2, and only 3. -- RayH ------------------------------------------------------------------------ RayH's Profile: http://www.excelforum.com/member.php...o&userid=36647 View this thread: http://www.excelforum.com/showthread...hreadid=564005 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count Specific Item in Specific Row
RayH Wrote: I'm a newbie and sorry about the nomenclature. I want to be able to count specific entries in Columns B and C against a specific criteria in Column A. Let's say the sheet has 100 rows of three columns (A,B,C). Column A rows will have entered 1, 2, or 3. Columns B and C will have entered W, X, or Y. So we can have 1 W W, 2 W X, 1 X X, 3 Y Y, etc. What I want to be able to do is to determine how many W X and Y EACH are in the rows with only 1, only 2, and only 3. So, basically, you want a table with the headers W, X, Y and rows 1, 2, 3 with the counts of each? Let's say you put such a table in the cells D1:G4, so the W,X,Y are in E1:G1 and 1,2,3 are in D2:D4. Then set E2=SUMPRODUCT(--($A$1:$A$100=$D2),(--($B$1:$B$100=E$1))+(--($C$1:$C$100=E$1))) Fill this down and across to complete the table. HTH, Scott -- Maistrye ------------------------------------------------------------------------ Maistrye's Profile: http://www.excelforum.com/member.php...o&userid=36078 View this thread: http://www.excelforum.com/showthread...hreadid=564005 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count Specific Item in Specific Row
The cell says ##VALUE. -- RayH ------------------------------------------------------------------------ RayH's Profile: http://www.excelforum.com/member.php...o&userid=36647 View this thread: http://www.excelforum.com/showthread...hreadid=564005 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count Specific Item in Specific Row
You can try this:
=SUMPRODUCT(($A$1:$A$10=$D2)*($B$1:$C$10=E$1)) And copy across and then down. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "RayH" wrote in message ... The cell says ##VALUE. -- RayH ------------------------------------------------------------------------ RayH's Profile: http://www.excelforum.com/member.php...o&userid=36647 View this thread: http://www.excelforum.com/showthread...hreadid=564005 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count Specific Item in Specific Row
Can someone help me interpet the rows and cells in the formula so I can transfer it to the spreadsheet that I'm actually using? I'm using rows 15-1000 for potential entries. Column A will have entries 1-31. Columns G,J,andM will have entries C,36,MH,NB. Yes, I am trying to have Columns for C, 36, MH, NB and rows 1-31. -- RayH ------------------------------------------------------------------------ RayH's Profile: http://www.excelforum.com/member.php...o&userid=36647 View this thread: http://www.excelforum.com/showthread...hreadid=564005 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count Specific Item in Specific Row
It must be the late hour here, but I don't understand a word you're saying
in describing your datalist, in relation to what you originally posted. Try describing it again ... explicitly. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "RayH" wrote in message ... Can someone help me interpet the rows and cells in the formula so I can transfer it to the spreadsheet that I'm actually using? I'm using rows 15-1000 for potential entries. Column A will have entries 1-31. Columns G,J,andM will have entries C,36,MH,NB. Yes, I am trying to have Columns for C, 36, MH, NB and rows 1-31. -- RayH ------------------------------------------------------------------------ RayH's Profile: http://www.excelforum.com/member.php...o&userid=36647 View this thread: http://www.excelforum.com/showthread...hreadid=564005 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count Specific Item in Specific Row
I thought the formula would be easier to understand and transpose to my actual spreadsheet. But I understand that the principle of what I am trying to extrapolate can be done. What I am trying to do is to track the services for clients on a daily basis. We will change sheets monthly. We deal with 15-25 clients daily, each of whom may use 1-3 of four services. In the actual sheet, Column A is the date, as represented by a two digit number for the day of the month: 01-31. Column B has the client name. Columns G, J, and M list the shifts (Morning, Afternoon, Afterhours). Clients might receive services coded as C, 36, MH, and NB. Any of these four codes can be entered in G, J, M. So on each day I'll have one row for each client or about 15-25 rows of information in columns g, j, and m I want to be able to track. (c, 36, mh, and nb) -- RayH ------------------------------------------------------------------------ RayH's Profile: http://www.excelforum.com/member.php...o&userid=36647 View this thread: http://www.excelforum.com/showthread...hreadid=564005 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count Specific Item in Specific Row
This description is far different from your OP!
You should make a unique list of your clients, say from N2 down to N26 (25 clients), Matching *exactly* with the entries in Column B. In N1, you'll enter the day you wish to examine (poll). This N1 entry *must* match the format that you're using in Column A, Whether it's a simple number depicting the day, or if you're using a real date format. In O1 to R1 you'll enter your service codes. Needles to say, these entries must match *exactly* with the codes in Columns G, J, and M. Enter this formula in O2 and copy across to R2, Then copy the O2:R2 range down to the row of your last client name. =SUMPRODUCT(($A$1:$A$1000=$N$1)*($B$1:$B$1000=$N2) *($G$1:$M$1000=O$1)) This formula assumes there are *no* service code data values in Columns H, I, K, and L. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "RayH" wrote in message ... I thought the formula would be easier to understand and transpose to my actual spreadsheet. But I understand that the principle of what I am trying to extrapolate can be done. What I am trying to do is to track the services for clients on a daily basis. We will change sheets monthly. We deal with 15-25 clients daily, each of whom may use 1-3 of four services. In the actual sheet, Column A is the date, as represented by a two digit number for the day of the month: 01-31. Column B has the client name. Columns G, J, and M list the shifts (Morning, Afternoon, Afterhours). Clients might receive services coded as C, 36, MH, and NB. Any of these four codes can be entered in G, J, M. So on each day I'll have one row for each client or about 15-25 rows of information in columns g, j, and m I want to be able to track. (c, 36, mh, and nb) -- RayH ------------------------------------------------------------------------ RayH's Profile: http://www.excelforum.com/member.php...o&userid=36647 View this thread: http://www.excelforum.com/showthread...hreadid=564005 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count Specific Item in Specific Row
Thanks for all the help. -- RayH ------------------------------------------------------------------------ RayH's Profile: http://www.excelforum.com/member.php...o&userid=36647 View this thread: http://www.excelforum.com/showthread...hreadid=564005 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count Specific Item in Specific Row
You're welcome ... And thank you for the feed-back!
-- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "RayH" wrote in message ... Thanks for all the help. -- RayH ------------------------------------------------------------------------ RayH's Profile: http://www.excelforum.com/member.php...o&userid=36647 View this thread: http://www.excelforum.com/showthread...hreadid=564005 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| count specific value with filtered data | Excel Worksheet Functions | |||
| count by specific text color in range of cell | Excel Discussion (Misc queries) | |||
| How do I count a value if another field = a specific value? Excel | Excel Worksheet Functions | |||
| Count cells with specific values in the cells next to them? | Excel Worksheet Functions | |||
| SPECIFIC WORD COUNT FROM A RANGE | Excel Discussion (Misc queries) | |||
Linear Mode
