CountIF When Looking at Other Data

[Amosbroker]

I am looking to count values if there is data present in other columns. I am creating a cohort trend and only want to count records for which both years have a value. See below, I do not want to count previous or current year category due to current value being unknown for red record. How do incorporate a CountIf that only counts records that have either good, borderline, or too high in each category in each year?

Member ID	Previous Value	Category	Current Value	Category	Risk Movement
IDXX	115	Good	115	Good	No Movement
IDxx	178	Too High	178	Too High	No Movement
ID123	143	Borderline	143	Borderline	No Movement
ID1234	108	Good	101	Good	No Movement
ID12345	100	Good		Unknown

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>

Thanks a ton!

 

[jtakw]

Hi,

Not exactly clear on what you want.

If you want a Separate count for each Year/Column, then use H1 formula (change range for the other column).
If you want a Combined count for both Year/Columns, then use I1 formula.

Book1
	A	B	C	D	E	F	G	H	I
1	Member ID	Previous Value	Category	Current Value	Category	Risk Movement		5	4
2	IDXX	115	Good	115	Good	No Movement
3	IDxx	178	Too High	178	Too High	No Movement
4	ID123	143	Borderline	143	Borderline	No Movement
5	ID1234	108	Good	101	Good	No Movement
6	ID12345	100	Good		Unknown
Sheet54

Cell Formulas
Range	Formula
H1	=SUM(COUNTIFS(C2:C6,{"Good","Borderline","Too High"}))
I1	=SUM(COUNTIFS(C2:C6,{"Good","Borderline","Too High"},E2:E6,{"Good","Borderline","Too High"}))

 

[Amosbroker]

I need the Good, Borderline and Too High Count for each year but only the years that have values for each year. So for example, on line 6 - I would not count the Good for column C because Year 2 Column F is unknown. Does that make sense?

 

[Amosbroker]

Basically I need to count each Good, Borderline, and Too High - but ONLY if there is data for each year. If there is an unknown for either of the years, then I do not want to count the data in either year. I am only assessing values that have available data in both years.

 

[jtakw]

If the I1 formula in my post #2 is not doing what you're describing, please show sample results, then I can adjust the formula.

 

[Amosbroker]

Thanks for the reply jtakw - I ended up using a few array formulas to do the the job:

SUM(COUNTIFS($L$2:$L$1000,{"Good"},$N$2:$N$1000,{"Good";"Too High";"Borderline"}))

I swapped out Good, Too High and Borderline for the formulas that followed.

 

[jtakw]

Still not sure what you want, but Glad you've got it working.

The formula you posted seems like a Regular formula rather than an array formula...