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?


[TABLE="width: 601"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Member ID[/TD]
[TD]Previous Value[/TD]
[TD]Category [/TD]
[TD]Current Value[/TD]
[TD]Category [/TD]
[TD]Risk Movement [/TD]
[/TR]
[TR]
[TD]IDXX[/TD]
[TD]115[/TD]
[TD]Good[/TD]
[TD]115[/TD]
[TD]Good[/TD]
[TD]No Movement[/TD]
[/TR]
[TR]
[TD]IDxx[/TD]
[TD]178[/TD]
[TD]Too High[/TD]
[TD]178[/TD]
[TD]Too High[/TD]
[TD]No Movement[/TD]
[/TR]
[TR]
[TD]ID123[/TD]
[TD]143[/TD]
[TD]Borderline[/TD]
[TD]143[/TD]
[TD]Borderline[/TD]
[TD]No Movement[/TD]
[/TR]
[TR]
[TD]ID1234[/TD]
[TD]108[/TD]
[TD]Good[/TD]
[TD]101[/TD]
[TD]Good[/TD]
[TD]No Movement[/TD]
[/TR]
[TR]
[TD]ID12345[/TD]
[TD]100[/TD]
[TD]Good[/TD]
[TD][/TD]
[TD]Unknown[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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...