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 structure: 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!

In C1 of Sheet 2 use (assuming no header rows
=SUMPRODUCT(--(Sheet1!A1:A100<>""),--(Sheet1!C1:C100>B1),--(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 structure: 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!
>

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 structure: 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!
>