Sumproduct & Indirect Functions

[VBA Noob]

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

 

[Biff]

Hi!

Try this:

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

Biff

 

[VBA Noob]

Thanks Biff

I was so close

VBA Noob

 

[Biff]

Are you sure this formula is doing what you want?

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

(D2247<=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")&"!D2247")=$B$1)*(INDIRECT(TEXT($A$24,"mmmmyy")&"!C2:C247")=$A2))

Biff

 

[VBA Noob]

Biff,

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

Thanks again for you help.

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

VBA Noo

 

[Biff]

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]

Biff,

It's 3 dates.

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

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

VBA Noob

 

[Bob Phillips]

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)

 

[Biff]

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

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

Biff

 

[VBA Noob]

No Problem

Thanks again man

VBA Noob

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

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

Biff

"VBA Noob" <[email protected]
wrote in
message news:[email protected]...
http://www.excelforum.com/showthread.php?threadid=561760