Count formula with multiple criteria

[shdawson]

Good Morning.


Need to do a logic evaluation. Thinking the COUNT function is the way to get there, but not certain.

Have this test data.

Excel Workbook
	C	D	E
1	Multiple	Dude	Team
2	N	Bob	Network
3	N	Jim	Database
4	Y	John	Database
5	Y	John	Dev
6	N	Tom	Security
Sheet1

Excel 2003

Need to find what columns in column D have more than 1 entry. Flag those in column C.

Any "simple" way to evaluate multiple columns in a counting action?


Thanks,
S

 

[prajul89]

=IF(COUNTIF(D:D,D1)>1,"Multiple",IF(COUNTIF(D:D,D1)=0,"N","Y"))


See if this helps

 

[id107]

=IF(COUNTIF(D:D,D1)>1,"Multiple",IF(COUNTIF(D:D,D1)=0,"N","Y"))


See if this helps

0 could never be a result if you're counting a cell within the range.

This should work:
=IF(COUNTIF(D:D,D2)=1,"N","Y")

 

[prajul89]

0 can be a result when the cell is blank

 

[shdawson]

=IF(COUNTIF(D:D,D2)=1,"N","Y")

...does pretty well, but still need to evaluate if there are multiple Dude names in the same Team. If there is one John per Team is the obstacle. (This is part of a data cleansing activity.)

Excel Workbook
	D	E
1	Dude	Team
2	Bob	Network
3	Jim	Database
4	John	Dev
5	John A.	Database
6	John A.	Database
7	John B.	Database
8	Tom	Security
Sheet1

Excel 2003
So, how can we evalaute both columns?

Thanks,
S

 

[prajul89]

if you are using 2007 then there is Countifs as formula try that.

 

[prajul89]

If you are using 2003 then you have to create concatenate for those fields and the countif that concatenate.

 

[shdawson]

OK, hmmmm......

Using 2003. Can you walk me through an example of your thoughts, please?


Thanks,
S

 

[id107]

0 can be a result when the cell is blank

Good point. I never thought of that

=IF(SUMPRODUCT(--(D$2:D$25=D2),--(E$2:E$25=E2))=1,"N","Y")

Replace 25 with the last row of data in both conditions.

 

[prajul89]

=D1&E1
this formula in your A column

=IF(COUNTIF(A:A,A1)=1,"N","Y")
and then this formula with reference to column A.