countifs month criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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)
 
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%
 
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.
 
Let's split this apart then

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

=SUMPRODUCT(--(YEAR(D2:D100)=2007),--(MONTH(D2:D100)=5))

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

=SUMPRODUCT(--(YEAR(D2:D100)=2007),--(MONTH(D2:D100)=5),--(L2:L100>0))
 
Sorted,

thanks very much , that will do nicely


Barb Reinhardt said:
Let's split this apart then

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

=SUMPRODUCT(--(YEAR(D2:D100)=2007),--(MONTH(D2:D100)=5))

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

=SUMPRODUCT(--(YEAR(D2:D100)=2007),--(MONTH(D2:D100)=5),--(L2:L100>0))
Click to expand...
 
Back
Top