sumproduct to count two arguments
 

I have an excel test so please bear with me that what I am asking for
may not be the best way to do this. Here is what I have been asked to
do.

I have daily dates from the beginning of this year to now and
corresponding data.

Using the sumproduct function I want to count the number of time that
the data is less than 1 and at the end of the month. Here is what I
have:

=SUMPRODUCT((--($K$3:$K$151=EOMONTH($K$3,1)))*--($A$3:$A$151<1))


I am stuck on the eomonth part..can anyone help?

Many thanks.

sumproduct to count two arguments
 

You're use of EOM is evaluating to the NEXT month:

K3 = 1/1/2006

EOMONTH(K3,1) = 2/28/2006

Maybe you want something like this:

=SUMPRODUCT(--($K$3:$K$151=EOMONTH($K$3,0)),--($A$3:$A$151<1))

Note that Sumproduct will evaluate EMPTY cells as 0 and this could cause a
problem with your second array: ($A$3:$A$151<1).

So, you may want to add another array to the formula to test for that:

=SUMPRODUCT(--($K$3:$K$151=EOMONTH($K$3,0)),--($A$3:$A$151<""),--($A$3:$A$151<1))

Biff

wrote in message
oups.com...
I have an excel test so please bear with me that what I am asking for
may not be the best way to do this. Here is what I have been asked to
do.

I have daily dates from the beginning of this year to now and
corresponding data.

Using the sumproduct function I want to count the number of time that
the data is less than 1 and at the end of the month. Here is what I
have:

=SUMPRODUCT((--($K$3:$K$151=EOMONTH($K$3,1)))*--($A$3:$A$151<1))


I am stuck on the eomonth part..can anyone help?

Many thanks.

sumproduct to count two arguments
 

this idea
=SUMPRODUCT(((ChecksA=EOMONTH(O6,1))*(ChecksD1)))
maybe
SUMPRODUCT((($K$3:$K$151=EOMONTH($K$3,1))*($A$3:$A $151<1)))

--
Don Guillett
SalesAid Software

wrote in message
oups.com...
I have an excel test so please bear with me that what I am asking for
may not be the best way to do this. Here is what I have been asked to
do.

I have daily dates from the beginning of this year to now and
corresponding data.

Using the sumproduct function I want to count the number of time that
the data is less than 1 and at the end of the month. Here is what I
have:

=SUMPRODUCT((--($K$3:$K$151=EOMONTH($K$3,1)))*--($A$3:$A$151<1))


I am stuck on the eomonth part..can anyone help?

Many thanks.

sumproduct to count two arguments
 

Gentlemen,

Thanks a ton for the responses. Though neither of them worked out the
way I wanted. The result is supposed to be six and I got 1. There
aren't any empty cells in either of the ranges. I'm thinking that I
may have an issue with date formatting..should it be in serial format?
I have been working on this problem for a few days now, asked numerous
people and I'm beginning think it is impossible.

Does anyone want to take a look at the workbook?



Biff wrote:
You're use of EOM is evaluating to the NEXT month:

K3 = 1/1/2006

EOMONTH(K3,1) = 2/28/2006

Maybe you want something like this:

=SUMPRODUCT(--($K$3:$K$151=EOMONTH($K$3,0)),--($A$3:$A$151<1))

Note that Sumproduct will evaluate EMPTY cells as 0 and this could cause a
problem with your second array: ($A$3:$A$151<1).

So, you may want to add another array to the formula to test for that:

=SUMPRODUCT(--($K$3:$K$151=EOMONTH($K$3,0)),--($A$3:$A$151<""),--($A$3:$A$151<1))

Biff

wrote in message
oups.com...
I have an excel test so please bear with me that what I am asking for
may not be the best way to do this. Here is what I have been asked to
do.

I have daily dates from the beginning of this year to now and
corresponding data.

Using the sumproduct function I want to count the number of time that
the data is less than 1 and at the end of the month. Here is what I
have:

=SUMPRODUCT((--($K$3:$K$151=EOMONTH($K$3,1)))*--($A$3:$A$151<1))


I am stuck on the eomonth part..can anyone help?

Many thanks.

sumproduct to count two arguments
 

Biff wrote:
You're use of EOM is evaluating to the NEXT month:

K3 = 1/1/2006

EOMONTH(K3,1) = 2/28/2006

Maybe you want something like this:

=SUMPRODUCT(--($K$3:$K$151=EOMONTH($K$3,0)),--($A$3:$A$151<1))

Note that Sumproduct will evaluate EMPTY cells as 0 and this could cause a
problem with your second array: ($A$3:$A$151<1).

So, you may want to add another array to the formula to test for that:

=SUMPRODUCT(--($K$3:$K$151=EOMONTH($K$3,0)),--($A$3:$A$151<""),--($A$3:$A$151<1))

Biff

wrote in message
oups.com...
I have an excel test so please bear with me that what I am asking for
may not be the best way to do this. Here is what I have been asked to
do.

I have daily dates from the beginning of this year to now and
corresponding data.

Using the sumproduct function I want to count the number of time that
the data is less than 1 and at the end of the month. Here is what I
have:

=SUMPRODUCT((--($K$3:$K$151=EOMONTH($K$3,1)))*--($A$3:$A$151<1))


I am stuck on the eomonth part..can anyone help?

Many thanks.

sumproduct to count two arguments
 

Try this:

Just a hunch that this is what you're trying to do:

=SUMPRODUCT(--(K$3:K$151=DATE(YEAR(K$3:K$151),MONTH(K$3:K$151)+1 ,0)),--(A$3:A$151<1))

You want to count based on *EVERY* EOM date?

Biff

wrote in message
ps.com...
Gentlemen,

Thanks a ton for the responses. Though neither of them worked out the
way I wanted. The result is supposed to be six and I got 1. There
aren't any empty cells in either of the ranges. I'm thinking that I
may have an issue with date formatting..should it be in serial format?
I have been working on this problem for a few days now, asked numerous
people and I'm beginning think it is impossible.

Does anyone want to take a look at the workbook?



Biff wrote:
You're use of EOM is evaluating to the NEXT month:

K3 = 1/1/2006

EOMONTH(K3,1) = 2/28/2006

Maybe you want something like this:

=SUMPRODUCT(--($K$3:$K$151=EOMONTH($K$3,0)),--($A$3:$A$151<1))

Note that Sumproduct will evaluate EMPTY cells as 0 and this could cause
a
problem with your second array: ($A$3:$A$151<1).

So, you may want to add another array to the formula to test for that:

=SUMPRODUCT(--($K$3:$K$151=EOMONTH($K$3,0)),--($A$3:$A$151<""),--($A$3:$A$151<1))

Biff

wrote in message
oups.com...
I have an excel test so please bear with me that what I am asking for
may not be the best way to do this. Here is what I have been asked to
do.

I have daily dates from the beginning of this year to now and
corresponding data.

Using the sumproduct function I want to count the number of time that
the data is less than 1 and at the end of the month. Here is what I
have:

=SUMPRODUCT((--($K$3:$K$151=EOMONTH($K$3,1)))*--($A$3:$A$151<1))


I am stuck on the eomonth part..can anyone help?

Many thanks.

sumproduct to count two arguments
 

Has anyone every called you genius? If not, let me be the first.
Thanks a ton!


Biff wrote:
Try this:

Just a hunch that this is what you're trying to do:

=SUMPRODUCT(--(K$3:K$151=DATE(YEAR(K$3:K$151),MONTH(K$3:K$151)+1 ,0)),--(A$3:A$151<1))

You want to count based on *EVERY* EOM date?

Biff

wrote in message
ps.com...
Gentlemen,

Thanks a ton for the responses. Though neither of them worked out the
way I wanted. The result is supposed to be six and I got 1. There
aren't any empty cells in either of the ranges. I'm thinking that I
may have an issue with date formatting..should it be in serial format?
I have been working on this problem for a few days now, asked numerous
people and I'm beginning think it is impossible.

Does anyone want to take a look at the workbook?



Biff wrote:
You're use of EOM is evaluating to the NEXT month:

K3 = 1/1/2006

EOMONTH(K3,1) = 2/28/2006

Maybe you want something like this:

=SUMPRODUCT(--($K$3:$K$151=EOMONTH($K$3,0)),--($A$3:$A$151<1))

Note that Sumproduct will evaluate EMPTY cells as 0 and this could cause
a
problem with your second array: ($A$3:$A$151<1).

So, you may want to add another array to the formula to test for that:

=SUMPRODUCT(--($K$3:$K$151=EOMONTH($K$3,0)),--($A$3:$A$151<""),--($A$3:$A$151<1))

Biff

wrote in message
oups.com...
I have an excel test so please bear with me that what I am asking for
may not be the best way to do this. Here is what I have been asked to
do.

I have daily dates from the beginning of this year to now and
corresponding data.

Using the sumproduct function I want to count the number of time that
the data is less than 1 and at the end of the month. Here is what I
have:

=SUMPRODUCT((--($K$3:$K$151=EOMONTH($K$3,1)))*--($A$3:$A$151<1))


I am stuck on the eomonth part..can anyone help?

Many thanks.

sumproduct to count two arguments
 

You're welcome. Thanks for the feedback!

Biff

wrote in message
ups.com...
Has anyone every called you genius? If not, let me be the first.
Thanks a ton!


Biff wrote:
Try this:

Just a hunch that this is what you're trying to do:

=SUMPRODUCT(--(K$3:K$151=DATE(YEAR(K$3:K$151),MONTH(K$3:K$151)+1 ,0)),--(A$3:A$151<1))

You want to count based on *EVERY* EOM date?

Biff

wrote in message
ps.com...
Gentlemen,

Thanks a ton for the responses. Though neither of them worked out the
way I wanted. The result is supposed to be six and I got 1. There
aren't any empty cells in either of the ranges. I'm thinking that I
may have an issue with date formatting..should it be in serial format?
I have been working on this problem for a few days now, asked numerous
people and I'm beginning think it is impossible.

Does anyone want to take a look at the workbook?



Biff wrote:
You're use of EOM is evaluating to the NEXT month:

K3 = 1/1/2006

EOMONTH(K3,1) = 2/28/2006

Maybe you want something like this:

=SUMPRODUCT(--($K$3:$K$151=EOMONTH($K$3,0)),--($A$3:$A$151<1))

Note that Sumproduct will evaluate EMPTY cells as 0 and this could
cause
a
problem with your second array: ($A$3:$A$151<1).

So, you may want to add another array to the formula to test for that:

=SUMPRODUCT(--($K$3:$K$151=EOMONTH($K$3,0)),--($A$3:$A$151<""),--($A$3:$A$151<1))

Biff

wrote in message
oups.com...
I have an excel test so please bear with me that what I am asking for
may not be the best way to do this. Here is what I have been asked
to
do.

I have daily dates from the beginning of this year to now and
corresponding data.

Using the sumproduct function I want to count the number of time
that
the data is less than 1 and at the end of the month. Here is what I
have:

=SUMPRODUCT((--($K$3:$K$151=EOMONTH($K$3,1)))*--($A$3:$A$151<1))


I am stuck on the eomonth part..can anyone help?

Many thanks.