help with counting text in multiple columns?

[krasp]

Hello!! I am a newbie so I have to get out the usual thank the lord for the board and its users, etc. So...

I have text in clumns A and B but only want to count certain entries in A based on the entry in B.. Detailed question after the html code...

Thanks in advance!! :D

Book1.xls
	A	B	C	D	E	F
1	AGENT	ORDERSOURCE	COPLETED	SOURCE	TOTAL	DAVE
2	MIKE	DIAMOND	YES	DIAMOND	3	#VALUE!
3	DAVE	DIAMOND	YES	CLAY	2
4	JIM	MILLER	NO	MILLER	4
5	MIKE	CLAY	YES	TOTALS	9
6	MIKE	DIAMOND	YES
7	JIM	MILLER	NO	COMPLETED	6
8	JIM	MILLER	YES	NOTCOMPLETE	3
9	JIM	CLAY	NO
10	APRIL	MILLER	YES
needtextcountingformulas

I need a formula to count how many times DAVE received an order from DIAMOND.
I assumed something like =COUNT(B:B,"DIAMOND")*IF(A:A,DAVE) but that doesn’t work
ALSO
I need a formula to count how many times DAVE received an order from DIAMOND that were 'completed'.

 

[krasp]

Aladin, Also,
How would I write this formula if I am writing the formula on sheet2 and the data is on sheet1?

THANKS!!
Chris :D

 

[Aladin Akyurek]

pgc, I don't have sapces after the entries. Thank you for showing me an alternative counting method!! :D

Aladin, Can you use that same formula to also use coulmn C as criteria (only count A:A if MIKE, if B:B is DIAMOND and C:C is YES

I just jumped seeing you in trouble with constants in the formulas...

Here is a fuller exhibit...

Book2
	A	B	C	D	E	F	G	H	I
1						AGENT
2	AGENT	ORDER SOURCE	COPLETED	SOURCE	Total	MIKE	DAVE	JIM	APRIL
3	MIKE	DIAMOND	YES	DIAMOND	3	2	1	0	0
4	DAVE	DIAMOND	YES	CLAY	2	1	0	1	0
5	JIM	MILLER	NO	MILLER	4	0	0	3	1
6	MIKE	CLAY	YES	Total	9	3	1	4	1
7	MIKE	DIAMOND	YES
8	JIM	MILLER	NO	COMPLETED	6	3	1	1	1
9	JIM	MILLER	YES	NOT COMPLETE	3	0	0	3	0
10	JIM	CLAY	NO
11	APRIL	MILLER	YES
12
needtextcountingformulas

E3, copied down to E5:

=COUNTIF($B$3:$B$11,D3)

E6:

=SUM(E3:E5)

E8:

=COUNTIF($C$3:$C$11,"Yes")

E9:

=COUNTIF($C$3:$C$11,"No")

F3, copied across to I3 and down to row 5...

=SUMPRODUCT(--($A$3:$A$11=F$2),--($B$3:$B$11=$D3))

F6, copied across to I6:

=SUM(F3:F5)

F8, copied across to I8:

=SUMPRODUCT(--($A$3:$A$11=F$2),--($C$3:$C$11="Yes"))

F9, copied across to I9:

=SUMPRODUCT(--($A$3:$A$11=F$2),--($C$3:$C$11="No"))

 

[Aladin Akyurek]

Aladin, Also,
How would I write this formula if I am writing the formula on sheet2 and the data is on sheet1?

THANKS!!
Chris :D

Just prefix the ranges with the sheet name, e.g.,

$A$3:$A$11 ===> Sheet1!$A$3:$A$11

 

[krasp]

Aladin!! Thank you for saving me!! Much appreciation and thanks!!

Chris

 

[krasp]

F8, copied across to I8:

=SUMPRODUCT(--($A$3:$A$11=F$2),--($C$3:$C$11="Yes"))

F9, copied across to I9:

=SUMPRODUCT(--($A$3:$A$11=F$2),--($C$3:$C$11="No"))

I can't get these to work when i replicate them in my own sheet. They always add up to 0 ...??

 

[Aladin Akyurek]

F8, copied across to I8:

=SUMPRODUCT(--($A$3:$A$11=F$2),--($C$3:$C$11="Yes"))

F9, copied across to I9:

=SUMPRODUCT(--($A$3:$A$11=F$2),--($C$3:$C$11="No"))

I can't get these to work when i replicate them in my own sheet. They always add up to 0 ...??

A few things to check...

Have you added the sheet prefix to the ranges?

Does F$2 refer to a name as is the case in the exhibit?

 

[Krishnakumar]

Hi,

Your data..

Book2
	A	B	C	D
1	AGENT	ORDER SOURCE	COPLETED
2	MIKE	DIAMOND	YES
3	DAVE	DIAMOND	YES
4	JIM	MILLER	NO
5	MIKE	CLAY	YES
6	MIKE	DIAMOND	YES
7	JIM	MILLER	NO
8	JIM	MILLER	YES
9	JIM	CLAY	NO
10	APRIL	MILLER	YES
Sheet1

Result sheet...

Book2
	A	B	C	D
1	AGENT	ORDER SOURCE	COPLETED
2				9
3
4
5	9	DIAMOND	MILLER	CLAY
6	MIKE	2	0	1
7	DAVE	1	0	0
8	JIM	0	3	1
9	APRIL	0	1	0
Sheet2

Define Database,

Refers to box: =Sheet1!$A$1:$C$10

A5 on Sheet2,

=DCOUNTA(Database,1,A1:C2)

Select A5:D9

Go to Data > Table >

Row Input Cell: B2
Column input cell: A2

Ok

If you want a particular result type the appropriate source, status in B2 and C2 respectively.

HTH