sumproduct to count two arguments

  • Thread starter Thread starter bpkaufman
  • Start date Start date
B

bpkaufman

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.
 
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
 
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
 
this idea
=SUMPRODUCT(((ChecksA=EOMONTH(O6,1))*(ChecksD>1)))
maybe
SUMPRODUCT((($K$3:$K$151=EOMONTH($K$3,1))*($A$3:$A$151<1)))
 
this idea
=SUMPRODUCT(((ChecksA=EOMONTH(O6,1))*(ChecksD>1)))
maybe
SUMPRODUCT((($K$3:$K$151=EOMONTH($K$3,1))*($A$3:$A$151<1)))
 
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?
 
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 said:
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
Click to expand...
 
Biff said:
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
Click to expand...
 
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
 
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Sumproduct neither nor 4
SUMPRODUCT has mismatched range sizes error when sheet opened inGoogle Excel. 1
Sumproduct Help! 2
SUMPRODUCT arguments 3
SUMPRODUCT by Date 3
Sumproduct 1
Sumproduct - Second Attempt 4
Scrabble Value calculation for Welsh words 0

Back
Top