Count Function 2 Criteria

[Tucker2008]

I need to match a criteria in one column of data and count the results from a different column in the table.

So I want to reference a Cell A2 on Sheet1....if cell A2 is "4/1/2015"

I want a formula that does the following

Reference Cell A2, Against the Table Below, and Return a Count of 3.

There are 4 instances of "4/1/2015" but only 3 have Values in them. they are all different and I want to count them.



DATA1, Sheet 2

COL A	COL B
4/1/2015
4/1/2015	CAT
4/1/2015	PEN
4/1/2015	DOG

<tbody>
</tbody>

Result: 4/1/2015 = 3

<tbody>
</tbody>

 

[mrshl9898]

In B2

=COUNTIFS(Sheet2!A:A,A2,Sheet2!B:B,"<>"&"")

 

[Tucker2008]

In B2

=COUNTIFS(Sheet2!A:A,A2,Sheet2!B:B,"<>"&"")

Thank You! --- Can you walk me through this formula? The Significance of the "<>" and the "&" ??

 

[mrshl9898]

The <> is does not equal.

The & I use as <> is a bit funny in some functions. By typing "<>"&"" you are basically Concatenating the two. Where "<>""" or <>"" doesn't always work.

 

[Aladin Akyurek]

=COUNTIFS(Sheet2!A:A,A2,Sheet2!B:B,"?*")

since you seem to have text (no numbers) in column B.<!-- edit note -->

 

[Tucker2008]

Thank you for the explanations, I appreciate it.

 

[Aladin Akyurek]

The <> is does not equal.

The & I use as <> is a bit funny in some functions. By typing "<>"&"" you are basically Concatenating the two. Where "<>""" or <>"" doesn't always work.

The idiom is just "<>"...

=COUNTIFS(Sheet2!A:A,A2,Sheet2!B:B,"<>")

This excludes only empty, unused cells. This means it picks out values like FALSE, #N/A, a formula blank (i.e. ""), text, and numbers.

 

[mrshl9898]

Cheers Aladin!