|
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$2 47="&$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 |
Sumproduct & Indirect Functions
Hi!
Try this: =SUMPRODUCT((INDIRECT(TEXT($A$24,"mmmmyy")&"!D2:D2 47")<=$B$1)*(INDIRECT(TEXT($A$24,"mmmmyy")&"!Y2:Y2 47")=$B$1)*(INDIRECT(TEXT($A$24,"mmmmyy")&"!C2:C2 47")=$A2)) Biff "VBA Noob" wrote in message ... 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$2 47="&$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 |
Sumproduct & Indirect Functions
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 |
Sumproduct & Indirect Functions
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:D2 47")=$B$1)*(INDIRECT(TEXT($A$24,"mmmmyy")&"!C2:C24 7")=$A2)) Biff "VBA Noob" wrote in message ... 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 |
Sumproduct & Indirect Functions
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 |
Sumproduct & Indirect Functions
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" wrote in message ... 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 |
Sumproduct & Indirect Functions
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 |
Sumproduct & Indirect Functions
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" wrote in message ... 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 |
Sumproduct & Indirect Functions
Oh....ok.....I see.
I was just having a "blockhead" moment! I have those every now and then. Biff "VBA Noob" wrote in message ... 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 |
Sumproduct & Indirect Functions
No Problem Thanks again man VBA Noob Biff Wrote: Oh....ok.....I see. I was just having a "blockhead" moment! I have those every now and then. Biff "VBA Noob" wrote in message ... 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 -- 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 |
| All times are GMT +1. The time now is 06:55 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com