Sumproduct & Indirect Functions

Hi all,

Can someone help with this formula,

Cell $A$24 = A cell formatted as Month and Year = July06
Cell $B$1 = a date 1/7/06 linked to $A$24

Trying to use the indirect function to ref a sheet called July06 and other ranges here a example of one range =July06!$D$2:$D$247

This is what I've got

=SUMPRODUCT(--(INDIRECT(TEXT($A$24,"mmmmyy")&"!$D$2:$D$247<="&$B$1)*(INDIRECT(TEXT($A$24,"mmmmyy")&"!$Y$2:$Y$247>="&$B$1)*(INDIRECT(TEXT($A$24,"mmmmyy")&"!$C$2:$C$247="&$A2)))))

Any help would be appreciated


VBA Noob

Hi!

Try this:

=SUMPRODUCT((INDIRECT(TEXT($A$24,"mmmmyy")&"!D2:D247")<=$B$1)*(INDIRECT(TEXT($A$24,"mmmmyy")&"!Y2:Y247")>=$B$1)*(INDIRECT(TEXT($A$24,"mmmmyy")&"!C2:C247")=$A2))

Biff

"VBA Noob" <[email protected]> wrote in
message news:[email protected]...
>
> Hi all,
>
> Can someone help with this formula,
>
> Cell $A$24 = A cell formatted as Month and Year = July06
> Cell $B$1 = a date 1/7/06 linked to $A$24
>
> Trying to use the indirect function to ref a sheet called July06 and
> other ranges here a example of one range =July06!$D$2:$D$247
>
> This is what I've got
>
> =SUMPRODUCT(--(INDIRECT(TEXT($A$24,"mmmmyy")&"!$D$2:$D$247<="&$B$1)*(INDIRECT(TEXT($A$24,"mmmmyy")&"!$Y$2:$Y$247>="&$B$1)*(INDIRECT(TEXT($A$24,"mmmmyy")&"!$C$2:$C$247="&$A2)))))
>
> Any help would be appreciated
>
>
> VBA Noob
>
>
> --
> VBA Noob
> ------------------------------------------------------------------------
> VBA Noob's Profile:
> http://www.excelforum.com/member.php...o&userid=33833
> View this thread: http://www.excelforum.com/showthread...hreadid=561760
>

Thanks Biff

I was so close

VBA Noob

Are you sure this formula is doing what you want?

If B1 = 1/7/06 (1 July 2006), then:

(D2:D247<=B1)*(Y2:Y247>=B1)

Is only counting entries that =B1.

If that's what you want then you can eliminate one of the arrays:

=SUMPRODUCT((INDIRECT(TEXT($A$24,"mmmmyy")&"!D2:D247")=$B$1)*(INDIRECT(TEXT($A$24,"mmmmyy")&"!C2:C247")=$A2))

Biff

"VBA Noob" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks Biff
>
> I was so close
>
> VBA Noob
>
>
> --
> VBA Noob
> ------------------------------------------------------------------------
> VBA Noob's Profile:
> http://www.excelforum.com/member.php...o&userid=33833
> View this thread: http://www.excelforum.com/showthread...hreadid=561760
>

Biff,

It's counting days between a start and an end date that matchs a third criteria.

Thanks again for you help.

May not use it as I can't use on closed workbook.

VBA Noob

> It's counting days between a start and an end date that matchs a third
> criteria.

But you're not using 2 dates in your comparison, you're only using cell B1.
So, the only dates that are both <= and >= is 1 July 2006 (B1).

Unless I'm missing something?

Biff

"VBA Noob" <[email protected]> wrote in
message news:[email protected]...
>
> Biff,
>
> It's counting days between a start and an end date that matchs a third
> criteria.
>
> Thanks again for you help.
>
> May not use it as I can't use on closed workbook.
>
> VBA Noob
>
>
> --
> VBA Noob
> ------------------------------------------------------------------------
> VBA Noob's Profile:
> http://www.excelforum.com/member.php...o&userid=33833
> View this thread: http://www.excelforum.com/showthread...hreadid=561760
>

Biff,

It's 3 dates.

B1 = 1/7/06 Date to match A2 = Criteria to match

Range D2:D247 is start date
Range Y2:Y247 is the End Date
Range C2:C247 is the Criteria

VBA Noob

But it won't work with a closed workbook, INDIRECT just doesn't work.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"VBA Noob" <[email protected]> wrote in
message news:[email protected]...
>
> Biff,
>
> It's 3 dates.
>
> B1 = 1/7/06 Date to match A2 = Criteria to match
>
> Range D2:D247 is start date
> Range Y2:Y247 is the End Date
> Range C2:C247 is the Criteria
>
> VBA Noob
>
>
> --
> VBA Noob
> ------------------------------------------------------------------------
> VBA Noob's Profile:
http://www.excelforum.com/member.php...o&userid=33833
> View this thread: http://www.excelforum.com/showthread...hreadid=561760
>

Oh....ok.....I see.

I was just having a "blockhead" moment! I have those every now and then.

Biff

"VBA Noob" <[email protected]> wrote in
message news:[email protected]...
>
> Biff,
>
> It's 3 dates.
>
> B1 = 1/7/06 Date to match A2 = Criteria to match
>
> Range D2:D247 is start date
> Range Y2:Y247 is the End Date
> Range C2:C247 is the Criteria
>
> VBA Noob
>
>
> --
> VBA Noob
> ------------------------------------------------------------------------
> VBA Noob's Profile:
> http://www.excelforum.com/member.php...o&userid=33833
> View this thread: http://www.excelforum.com/showthread...hreadid=561760
>

No Problem

Thanks again man

VBA Noob

Originally Posted by Biff

Oh....ok.....I see.

I was just having a "blockhead" moment! I have those every now and then.

Biff

"VBA Noob" <[email protected]> wrote in
message news:[email protected]...
>
> Biff,
>
> It's 3 dates.
>
> B1 = 1/7/06 Date to match A2 = Criteria to match
>
> Range D2:D247 is start date
> Range Y2:Y247 is the End Date
> Range C2:C247 is the Criteria
>
> VBA Noob
>
>
> --
> VBA Noob
> ------------------------------------------------------------------------
> VBA Noob's Profile:
> http://www.excelforum.com/member.php...o&userid=33833
> View this thread: http://www.excelforum.com/showthread...hreadid=561760
>