Count Specific Item in Specific Row

[RayH]

I'm a newbie and sorry about the nomenclature. I want to be able t
count specific entries in Columns B and C against a specific criteri
in Column A.

Let's say the sheet has 100 rows of three columns (A,B,C). Column
rows will have entered 1, 2, or 3. Columns B and C will have entere
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 abl
to do is to determine how many W X and Y EACH are in the rows with onl
1, only 2, and only 3

 

[Maistrye]

I'm a newbie and sorry about the nomenclature. I want to be able t
count specific entries in Columns B and C against a specific criteri
in Column A.

Let's say the sheet has 100 rows of three columns (A,B,C). Column
rows will have entered 1, 2, or 3. Columns B and C will have entere
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 abl
to do is to determine how many W X and Y EACH are in the rows with onl
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 i
E1:G1 and 1,2,3 are in D24.

Then se
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,
Scot

 

[RayH]

The cell says ##VALUE.

 

[RagDyeR]

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



The cell says ##VALUE.

 

[RayH]

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.

 

[Ragdyer]

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.

 

[RayH]

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]

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.

 

[RayH]

Thanks for all the help

 

[Ragdyer]

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