countifs month criteria
 

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

countifs month criteria
 

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)
--
HTH,
Barb Reinhardt



"brian.mccreery" wrote:

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

countifs month criteria
 

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.

"Barb Reinhardt" wrote:

Can you describe exactly what you want to do?

Thanks,
Barb Reinhardt



"brian.mccreery" wrote:


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%


"Barb Reinhardt" wrote:

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)
--
HTH,
Barb Reinhardt



"brian.mccreery" wrote:

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

countifs month criteria
 

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:L1000))

--
HTH,
Barb Reinhardt



"brian.mccreery" wrote:

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.

"Barb Reinhardt" wrote:

Can you describe exactly what you want to do?

Thanks,
Barb Reinhardt



"brian.mccreery" wrote:


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%


"Barb Reinhardt" wrote:

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)
--
HTH,
Barb Reinhardt



"brian.mccreery" wrote:

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

countifs month criteria
 

Sorted,

thanks very much , that will do nicely


"Barb Reinhardt" wrote:

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:L1000))

--
HTH,
Barb Reinhardt



"brian.mccreery" wrote:

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.

"Barb Reinhardt" wrote:

Can you describe exactly what you want to do?

Thanks,
Barb Reinhardt



"brian.mccreery" wrote:


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%


"Barb Reinhardt" wrote:

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)
--
HTH,
Barb Reinhardt



"brian.mccreery" wrote:

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