countifs month criteria

[Guest]

Hi, I am stuck trying to simplify formula. Have Excel 2007 and want to use
COUNTIFS ? to incorporate dates in column D for the current month. total rows
currently 1800
D L
02/08/2007 -1
01/09/2007 3
02/10/2007 4
04/10/2007 -3

This is my current working formula without using month

COUNTIF(L:L,">0")/(COUNTIF(L:L,">0")+COUNTIF(L:L,"<0"))

many thanks if someone can help

 

[Guest]

Let's say your data is in A2:B1800

=SUMPRODUCT(--(YEAR(A2:A1800)=YEAR(TODAY())),--(MONTH(A2:A1800)=MONTH(TODAY())),--(B2:B1800 <> 0))/COUNTA(A2:A1800)

 

[Guest]

Sorry Barb but I can't get it to work. I assumed the (B2:B1800 <>
0))/COUNTA(A2:A1800) was wrong but even then I am coming up with a figure
which is totally wrong like 6.2% instead of 58%

 

[Guest]

Can you describe exactly what you want to do?

Thanks,
Barb Reinhardt

 

[Guest]

Im a spreadbet trader . I want to see the %age of trades that are in the
positive side over a period of time. In this case a month. Column D is the
entered date and Column L is the result of the transaction. Obviously the
example I gave, gives the overall %age. Hope that explains better.

 

[Guest]

Let's split this apart then

To count the number of transactions in a May 2005, do this

=SUMPRODUCT(--(YEAR(D2100)=2007),--(MONTH(D2100)=5))

To count the number of POSITIVE transactions in May 2005, do this

=SUMPRODUCT(--(YEAR(D2100)=2007),--(MONTH(D2100)=5),--(L2:L100>0))

 

[Guest]

Sorted,

thanks very much , that will do nicely

Let's split this apart then

To count the number of transactions in a May 2005, do this

=SUMPRODUCT(--(YEAR(D2100)=2007),--(MONTH(D2100)=5))

To count the number of POSITIVE transactions in May 2005, do this

=SUMPRODUCT(--(YEAR(D2100)=2007),--(MONTH(D2100)=5),--(L2:L100>0))