Can anyone shorten this one. (not overly clear on array usage)

[Down'd Pilot]

I'm sure there is a way to shorten this formula, and i'm sure its by using arrays. But i'm very uneducated when it comes to using arrays fully

=SUMIF('4 Week 4cast'!C2:C483,TODAY()+22,'4 Week 4cast'!Q2:Q483)+SUMIF('4 Week 4cast'!C2:C483,TODAY()+23,'4 Week 4cast'!Q2:Q483)+SUMIF('4 Week 4cast'!C2:C483,TODAY()+24,'4 Week 4cast'!Q2:Q483)+SUMIF('4 Week 4cast'!C2:C483,TODAY()+25,'4 Week 4cast'!Q2:Q483)+SUMIF('4 Week 4cast'!C2:C483,TODAY()+26,'4 Week 4cast'!Q2:Q483)+SUMIF('4 Week 4cast'!C2:C483,TODAY()+27,'4 Week 4cast'!Q2:Q483)+SUMIF('4 Week 4cast'!C2:C483,TODAY()+28,'4 Week 4cast'!Q2:Q483)

Basicaly just adding up times in one column based on a date range of one week.

[Bob Phillips]

=SUMIF('4 Week 4cast'!C2:C483,">="&TODAY()+22,'4 Week 4cast'!Q2:Q483)-
SUMIF('4 Week 4cast'!C2:C483,">"&TODAY()+28,'4 Week 4cast'!Q2:Q483)


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Down'd Pilot" <[email protected]>
wrote in message
news:[email protected]...
>
> I'm sure there is a way to shorten this formula, and i'm sure its by
> using arrays. But i'm very uneducated when it comes to using arrays
> fully
>
> =SUMIF('4 Week 4cast'!C2:C483,TODAY()+22,'4 Week
> 4cast'!Q2:Q483)+SUMIF('4 Week 4cast'!C2:C483,TODAY()+23,'4 Week
> 4cast'!Q2:Q483)+SUMIF('4 Week 4cast'!C2:C483,TODAY()+24,'4 Week
> 4cast'!Q2:Q483)+SUMIF('4 Week 4cast'!C2:C483,TODAY()+25,'4 Week
> 4cast'!Q2:Q483)+SUMIF('4 Week 4cast'!C2:C483,TODAY()+26,'4 Week
> 4cast'!Q2:Q483)+SUMIF('4 Week 4cast'!C2:C483,TODAY()+27,'4 Week
> 4cast'!Q2:Q483)+SUMIF('4 Week 4cast'!C2:C483,TODAY()+28,'4 Week
> 4cast'!Q2:Q483)
>
> Basicaly just adding up times in one column based on a date range of
> one week.
>
>
> --
> Down'd Pilot
> ------------------------------------------------------------------------
> Down'd Pilot's Profile:
http://www.excelforum.com/member.php...o&userid=34954
> View this thread: http://www.excelforum.com/showthread...hreadid=546864
>

[Don Guillett]

try this idea where b1 is your starting date
=SUMPRODUCT((A3:A23>=B1)*(A3:A23<B1+7)*B3:B23)

--
Don Guillett
SalesAid Software
[email protected]
"Down'd Pilot" <[email protected]>
wrote in message
news:[email protected]...
>
> I'm sure there is a way to shorten this formula, and i'm sure its by
> using arrays. But i'm very uneducated when it comes to using arrays
> fully
>
> =SUMIF('4 Week 4cast'!C2:C483,TODAY()+22,'4 Week
> 4cast'!Q2:Q483)+SUMIF('4 Week 4cast'!C2:C483,TODAY()+23,'4 Week
> 4cast'!Q2:Q483)+SUMIF('4 Week 4cast'!C2:C483,TODAY()+24,'4 Week
> 4cast'!Q2:Q483)+SUMIF('4 Week 4cast'!C2:C483,TODAY()+25,'4 Week
> 4cast'!Q2:Q483)+SUMIF('4 Week 4cast'!C2:C483,TODAY()+26,'4 Week
> 4cast'!Q2:Q483)+SUMIF('4 Week 4cast'!C2:C483,TODAY()+27,'4 Week
> 4cast'!Q2:Q483)+SUMIF('4 Week 4cast'!C2:C483,TODAY()+28,'4 Week
> 4cast'!Q2:Q483)
>
> Basicaly just adding up times in one column based on a date range of
> one week.
>
>
> --
> Down'd Pilot
> ------------------------------------------------------------------------
> Down'd Pilot's Profile:
> http://www.excelforum.com/member.php...o&userid=34954
> View this thread: http://www.excelforum.com/showthread...hreadid=546864
>

[macropod]

Hi Down'd Pilot,

Try:
=SUM(IF(('4 Week 4cast'!C2:C483>TODAY()+21)*('4 Week
4cast'!C2:C483<TODAY()+29),'4 Week4cast'!Q2:Q483,))
as an array formula (ie input with Ctrl-Shift-Enter).


Cheers


"Down'd Pilot" <[email protected]>
wrote in message
news:[email protected]...
>
> I'm sure there is a way to shorten this formula, and i'm sure its by
> using arrays. But i'm very uneducated when it comes to using arrays
> fully
>
> =SUMIF('4 Week 4cast'!C2:C483,TODAY()+22,'4 Week
> 4cast'!Q2:Q483)+SUMIF('4 Week 4cast'!C2:C483,TODAY()+23,'4 Week
> 4cast'!Q2:Q483)+SUMIF('4 Week 4cast'!C2:C483,TODAY()+24,'4 Week
> 4cast'!Q2:Q483)+SUMIF('4 Week 4cast'!C2:C483,TODAY()+25,'4 Week
> 4cast'!Q2:Q483)+SUMIF('4 Week 4cast'!C2:C483,TODAY()+26,'4 Week
> 4cast'!Q2:Q483)+SUMIF('4 Week 4cast'!C2:C483,TODAY()+27,'4 Week
> 4cast'!Q2:Q483)+SUMIF('4 Week 4cast'!C2:C483,TODAY()+28,'4 Week
> 4cast'!Q2:Q483)
>
> Basicaly just adding up times in one column based on a date range of
> one week.
>
>
> --
> Down'd Pilot
> ------------------------------------------------------------------------
> Down'd Pilot's Profile:
http://www.excelforum.com/member.php...o&userid=34954
> View this thread: http://www.excelforum.com/showthread...hreadid=546864
>

[Down'd Pilot]

Thanks for all the input, it was a great help