Count Specific Item in Specific Row

**RayH** · 07-22-2006, 01:26 PM

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.

**Maistrye** · 07-22-2006, 02:18 PM

Originally Posted by RayH

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

**RayH** · 07-22-2006, 04:42 PM

The cell says ##VALUE.

RagDyeR · 07-22-2006, 05:05 PM

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" <[email protected]> wrote in message
news:[email protected]...

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

**RayH** · 07-22-2006, 09:17 PM

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. Let's say I'll put this information starting in Column Q.

Ragdyer · 07-23-2006, 12:20 AM

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

**RayH** · 07-23-2006, 04:57 AM

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)

Ragdyer · 07-23-2006, 07:50 AM

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

**RayH** · 07-23-2006, 12:52 PM

Thanks for all the help.

Ragdyer · 07-23-2006, 02:55 PM

You're welcome ... And thank you for the feed-back!
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"RayH" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>