Need a formla to count

[levanoj]

I have a spreadsheet that appears as follows:

Book1
	A	B	C	D
1	PhoneID	SpeakerModel
2	200	spk8
3	200	spk8
4	200	spk8
5	200	spk8
6	200	spk8
7	200	spk16
8	201	spk8
9	201	spk8
10	201	spk16
11	201	spk16
12	202	spk8
13	202	spk8
14	202	spk8
15	202	spk8
16	203	spk16
17	203	spk16
18	204	spk16
19	204	spk16
20	204	spk16
21	204	spk16
22	204	spk8
Sheet2

And on another worksheet I have a table that looks as follows:

Book1
	A	B	C	D
1	PhoneID	spk8	spk16
2	200
3	201
4	202
5	203
6	204
Sheet1

What I'm looking for is a formula that I can enter in the blank spaces under column B & C that will count the number of appearnces the spkr8 or spkr16 value shows up for each of the phone ID's and provides a result as follows:

Book1
	A	B	C	D
1	PhoneID	spk8	spk16
2	200	5	1
3	201	2	2
4	202	4	0
5	203	0	2
6	204	2	4
Sheet1

Make sense? Can anyone help?

 

[Joe4]

Based on your example, place this formula in cell B2 and copy for all your cells in columns B and C:

=SUMPRODUCT(--(Sheet2!$A$2:$A$22=Sheet1!$A2),--(Sheet2!$B$2:$B$22=Sheet1!B$1))

 

[levanoj]

Thanks Joe4. That appears to have worked. I have a slight adjustment to the scenario below and am hoping that this is doable. Using my original example I have an additional column (C) that appears as follows:

Book2
	A	B	C	D
1	PhoneID	SpeakerModel	UserID
2	200	spk8	4010
3	200	spk8	N/A
4	200	spk8	4034
5	200	spk8	4050
6	200	spk8	N/A
7	200	spk16	N/A
8	201	spk8	5001
9	201	spk8	3030
10	201	spk16	3550
11	201	spk16	N/A
12	202	spk8	N/A
13	202	spk8	N/A
14	202	spk8	N/A
15	202	spk8	4201
16	203	spk16	5454
17	203	spk16	5455
18	204	spk16	5899
19	204	spk16	5944
20	204	spk16	N/A
21	204	spk16	N/A
22	204	spk8	3021
Sheet2

What I'd like is to have a count formula that will only count the spk8 & spk16 values if there's a numeric value next to that cell in column C above. So using the example above the result would be as follows:

Book2
	A	B	C	D
1	PhoneID	spk8	spk16
2	200	3	0
3	201	2	1
4	202	1	0
5	203	0	2
6	204	2	2
Sheet1

 

[Joe4]

Maybe this?

=SUMPRODUCT(--(Sheet2!$A$2:$A$22=Sheet1!$A2),--(Sheet2!$B$2:$B$22=Sheet1!C$1),--(Sheet2!$C$2:$C$22<>"N/A"))

 

[Aladin Akyurek]

Maybe this?

=SUMPRODUCT(--(Sheet2!$A$2:$A$22=Sheet1!$A2),
--(Sheet2!$B$2:$B$22=Sheet1!C$1),
--(Sheet2!$C$2:$C$22<>"N/A"))

--ISNUMBER(Sheet2!$C$2:$C$22)

might be more appropriate instead of

--(Sheet2!$C$2:$C$22<>"N/A")

 

[Joe4]

--ISNUMBER(Sheet2!$C$2:$C$22)

might be more appropriate instead of

--(Sheet2!$C$2:$C$22<>"N/A")

Agreed. That is a better solution.

=SUMPRODUCT(--(Sheet2!$A$2:$A$22=Sheet1!$A2),--(Sheet2!$B$2:$B$22=Sheet1!B$1),--ISNUMBER(Sheet2!$C$2:$C$22))

 

[levanoj]

Love the team work. This worked perfectly!! Thanks again Gents..