Can I have help with Multi COUNTIF

[Mettaya1]

I currently have this equation =COUNTIF($EZ29:$GD42,"TTMU")+COUNTIF($EZ29:$GD42,"TTMC")+COUNTIF($EZ29:$GD42,"TTMI")
Which works fine.

Was wondering if there was an easier way of doing this as typing it in is slow going when this is used month by month over a specific range.

Would a LOOKUP do this easier and if so how would it work.

Regards

Mettaya1

 

[Jonmo1]

Try
=SUM(COUNTIF($EZ29:$GD42,{"TTMU","TTMC","TTMI"}))

 

[Mettaya1]

That does work Jonmo1

Thanks

 

[Jonmo1]

Glad to help, thanks for the feedback..


You can also use a range of cells for the criteria
=SUMPRODUCT(COUNTIF($EZ29:$GD42,$A$1:$A$3))
A1:A3 = TTMU TTMC and TTMI

 

[Mettaya1]

I actually have a sheet arranged with the top row with dates and the first column is who is doing the work.
Could I do a lookup in some way to just find a particular month and the above criteria?

 

[Jonmo1]

Sorry, I don't follow.
Can you give some more detail?

You may be better off with a new thread.

 

[Mettaya1]

Sorry, I don't follow.
Can you give some more detail?

You may be better off with a new thread.

For example
A1:Z1 are dates
A2:A30 are roles of staff and companies

B2:Z30 are posted as either TTMU TTMC and TTMI if one took place. These are actually audits. So am trying to work out a way of picking this data up without having to type a complex calc in for each month.

Sorry I wasn't more clear earlier.

 

[Jonmo1]

Try

=INDEX($B$2:$Z$30,MATCH($A$32,$A$1:$A$30,0),MATCH($A$33,$B$1:$Z$1,0))

A32 = the Name of interest
A33 = the Date of interest

 

[Mettaya1]

Try

=INDEX($B$2:$Z$30,MATCH($A$32,$A$1:$A$30,0),MATCH($A$33,$B$1:$Z$1,0))

A32 = the Name of interest
A33 = the Date of interest

Came up with #N/A

 

[Jonmo1]

Works for me.
Although I had a small typo (that would not have caused #N/A error)

Perhaps the Match for the dates should be a closest match instead of exact match
=INDEX($B$2:$Z$30,MATCH($A$32,$A$2:$A$30,0),MATCH($A$33,$B$1:$Z$1))

Or I've misunderstood you're requirement.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		1/1/2013	2/1/2013	3/1/2013	4/1/2013	5/1/2013	6/1/2013	7/1/2013	8/1/2013	9/1/2013	10/1/2013	11/1/2013	12/1/2013
2	Name1	TTMU	TTMU	TTMI	TTMC	TTMU	TTMC	TTMC	TTMU	TTMU	TTMC	TTMU	TTMU
3	Name2	TTMI	TTMI	TTMI	TTMU	TTMC	TTMI	TTMU	TTMC	TTMI	TTMI	TTMC	TTMC
4	Name3	TTMC	TTMI	TTMU	TTMI	TTMC	TTMU	TTMI	TTMU	TTMC	TTMI	TTMI	TTMC
5	Name4	TTMI	TTMI	TTMC	TTMU	TTMU	TTMU	TTMI	TTMI	TTMI	TTMC	TTMU	TTMC
6	Name5	TTMI	TTMU	TTMU	TTMC	TTMC	TTMU	TTMI	TTMU	TTMI	TTMI	TTMC	TTMC
7	Name6	TTMI	TTMU	TTMI	TTMC	TTMI	TTMI	TTMU	TTMC	TTMU	TTMI	TTMU	TTMC
8	Name7	TTMI	TTMU	TTMC	TTMC	TTMU	TTMC	TTMC	TTMI	TTMU	TTMU	TTMI	TTMC
9	Name8	TTMI	TTMU	TTMC	TTMI	TTMC	TTMU	TTMC	TTMI	TTMI	TTMC	TTMC	TTMU
10	Name9	TTMC	TTMU	TTMI	TTMU	TTMC	TTMC	TTMU	TTMI	TTMI	TTMC	TTMC	TTMC
11
31
32	Name3
33	4/1/2013
34
35	TTMI
Sheet1