Date range condition on multiple rows to get a tally
 

This is a simplification but contains the problem.
Worksheet #1 is customer detail, including a column named date_granted
(column C, specifically). The value for the name appears to be fine,
referencing C2:C1048576. It also has a column (A) named Voucher_ID which is
text format.

Worksheet #2 has 3 columns: start_period, end_period, and total_vouchers.

User may enter any dates in start-period and end-period: end-period must be
greater than or equal to start-period. I'm leaving that edit up to the user.

I thought I'd figured out how to do this but I obviously haven't since my
result always comes out "1"...what I want to do is count every non-blank
Voucher_ID if its date_granted = start_period and < end-period (in other
words, is in the date range defined by the period). I want to display that
total count in total_vouchers, so the problem is in the function(s) I have
for total_vouchers.

I can't tell from the evaluation but it looks like I'm only getting a result
for detail row 2. Currently I'm using a nested structu AND inside an IF.
The second possibility for error might be the IF TRUE operation, which I now
have as a COUNTA(Voucher_ID) - maybe that's just overlaying instead of
incrementing?

I'm sure it's something dumb I'm doing, and it doesn't seem like it should
be that complicated. I'm a newbie, though, so what do I know? Can somebody
more experienced please give me the function or tell me where I might find a
sample to copy? Thanks!

Date range condition on multiple rows to get a tally
 

In C1 of Sheet 2 use (assuming no header rows)
=SUMPRODUCT(--(Sheet1!A1:A100<""),--(Sheet1!C1:C100B1),--(Sheet1!C1:C100<C1))
adjust 100 to your last row
and copy down

Add/adjust conditions as you want...

This will count only the rows which have non-blank id, date date in B1 and
date < date in C1

"cat52" wrote:

This is a simplification but contains the problem.
Worksheet #1 is customer detail, including a column named date_granted
(column C, specifically). The value for the name appears to be fine,
referencing C2:C1048576. It also has a column (A) named Voucher_ID which is
text format.

Worksheet #2 has 3 columns: start_period, end_period, and total_vouchers.

User may enter any dates in start-period and end-period: end-period must be
greater than or equal to start-period. I'm leaving that edit up to the user.

I thought I'd figured out how to do this but I obviously haven't since my
result always comes out "1"...what I want to do is count every non-blank
Voucher_ID if its date_granted = start_period and < end-period (in other
words, is in the date range defined by the period). I want to display that
total count in total_vouchers, so the problem is in the function(s) I have
for total_vouchers.

I can't tell from the evaluation but it looks like I'm only getting a result
for detail row 2. Currently I'm using a nested structu AND inside an IF.
The second possibility for error might be the IF TRUE operation, which I now
have as a COUNTA(Voucher_ID) - maybe that's just overlaying instead of
incrementing?

I'm sure it's something dumb I'm doing, and it doesn't seem like it should
be that complicated. I'm a newbie, though, so what do I know? Can somebody
more experienced please give me the function or tell me where I might find a
sample to copy? Thanks!

Date range condition on multiple rows to get a tally
 

Hi,

Try this:

=SUMPRODUCT(--(A2:A1048576<""),--(C2:C1048576=Sheet2!E2),--(C2:C1048576<=Sheet2!F2))

However I think this will do the job in your case also

=SUMPRODUCT(--(A:A<""),--(C:C=Sheet2!E2),--(C:C<=Sheet1!F2))

In both cases the start date is in E2 and the end date in F2

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"cat52" wrote:

This is a simplification but contains the problem.
Worksheet #1 is customer detail, including a column named date_granted
(column C, specifically). The value for the name appears to be fine,
referencing C2:C1048576. It also has a column (A) named Voucher_ID which is
text format.

Worksheet #2 has 3 columns: start_period, end_period, and total_vouchers.

User may enter any dates in start-period and end-period: end-period must be
greater than or equal to start-period. I'm leaving that edit up to the user.

I thought I'd figured out how to do this but I obviously haven't since my
result always comes out "1"...what I want to do is count every non-blank
Voucher_ID if its date_granted = start_period and < end-period (in other
words, is in the date range defined by the period). I want to display that
total count in total_vouchers, so the problem is in the function(s) I have
for total_vouchers.

I can't tell from the evaluation but it looks like I'm only getting a result
for detail row 2. Currently I'm using a nested structu AND inside an IF.
The second possibility for error might be the IF TRUE operation, which I now
have as a COUNTA(Voucher_ID) - maybe that's just overlaying instead of
incrementing?

I'm sure it's something dumb I'm doing, and it doesn't seem like it should
be that complicated. I'm a newbie, though, so what do I know? Can somebody
more experienced please give me the function or tell me where I might find a
sample to copy? Thanks!