CountA if that row has correct date

[celestersue]

So I am trying to calculate if there was any data in Column D and Column A has the correct date associated in that row:

A1= 4/1/2013
D1= data

A2= 4/2/2013
D2=

A3= 4/1/2013
D3= data

So my formula could be something like this:
=CountA(D:D), AND(A:A, "4/1/2013")
and it would come up with the answer: 2

However that formula isn't correct and I can't wrap my head around how to fix it. How do I adjust it so that it will only count the number of cells that have data in them if the date is equal to the date in the formula?

 

[Aladin Akyurek]

So I am trying to calculate if there was any data in Column D and Column A has the correct date associated in that row:

A1= 4/1/2013
D1= data

A2= 4/2/2013
D2=

A3= 4/1/2013
D3= data

So my formula could be something like this:
=CountA(D:D), AND(A:A, "4/1/2013")
and it would come up with the answer: 2

However that formula isn't correct and I can't wrap my head around how to fix it. How do I adjust it so that it will only count the number of cells that have data in them if the date is equal to the date in the formula?

Something like:

=SUMPRODUCT(--ISNUMBER(A2:A500),1-ISBLANK(A2:A500))

=SUMPRODUCT(--ISNUMBER(A2:A500),1-(A2:A500=""))

 

[celestersue]

Aladin, that works to calculate that there is data in that column and gives me a total, but it doesn't help me in narrowing it down so that I know that the date is equal to 4/1/2013 and there is data in that column on that row. So instead of counting all 24 cells that have data it should only give me the 2 that have the right date associated.

 

[Aladin Akyurek]

Aladin, that works to calculate that there is data in that column and gives me a total, but it doesn't help me in narrowing it down so that I know that the date is equal to 4/1/2013 and there is data in that column on that row. So instead of counting all 24 cells that have data it should only give me the 2 that have the right date associated.

I meant:

=SUMPRODUCT(--ISNUMBER(A2:A500),1-ISBLANK(D2:D500))

=SUMPRODUCT(--ISNUMBER(A2:A500),1-(D2:D500=""))

This counts all of the data and date records.

If you want to calculate the number of data points associated with 4/1/2013...

=SUMPRODUCT(--(A2:A500=F2),1-ISBLANK(D2:D500))

where F2 is a date of interest.

 

[dulitul]

=countifs(a1:a500,"4/1/2013",d1:d500,"<>""")

 

[celestersue]

Works great Aladin! Thanks!

I meant:

=SUMPRODUCT(--ISNUMBER(A2:A500),1-ISBLANK(D2:D500))

=SUMPRODUCT(--ISNUMBER(A2:A500),1-(D2:D500=""))

This counts all of the data and date records.

If you want to calculate the number of data points associated with 4/1/2013...

=SUMPRODUCT(--(A2:A500=F2),1-ISBLANK(D2:D500))

where F2 is a date of interest.

 

[Aladin Akyurek]

Works great Aladin! Thanks!

You are welcome. Thanks for providing feedback.