Simplify syntax of a Sum(IF... Array
 

{=SUM(IF((LEFT(TEXT($A6,"mm"),2))=((LEFT(TEXT('Wee kly Total -
2005'!$B$4:$B$123,"mm"),2))),'Weekly Total - 2005'!E$4:E$123,"-"))}


Can this be simplified ....


--
JustMe602
------------------------------------------------------------------------
JustMe602's Profile: http://www.excelforum.com/member.php...o&userid=27854
View this thread: http://www.excelforum.com/showthread...hreadid=474003

I've omitted the worksheet name, but have you tried something like this?

=SUMPRODUCT((MONTH($B$4:$B$123)=MONTH($A6))*E$4:$E $123)

Also, a Pivot Table may help. You can group dates by month, month and year,
etc.


On Thu, 6 Oct 2005 20:30:09 -0500, JustMe602
wrote:


{=SUM(IF((LEFT(TEXT($A6,"mm"),2))=((LEFT(TEXT('We ekly Total -
2005'!$B$4:$B$123,"mm"),2))),'Weekly Total - 2005'!E$4:E$123,"-"))}


Can this be simplified ....

One way, still keeping it as an *array* formula:

=SUM(IF(TEXT($A6,"mm")=TEXT('Weekly Total -
2005'!$B$4:$B$123,"mm"),'Weekly Total - 2005'!E$4:E$123,"-"))

If you could live with a zero instead of the dash ( - ), when no data meets
the criteria, you could try this *regular*, *non-array* formula:

=SUMPRODUCT((TEXT($A6,"mm")=TEXT('Weekly Total -
2005'!$B$4:$B$123,"mm"))*('Weekly Total - 2005'!E$4:E$123))


"JustMe602" wrote
in message ...

{=SUM(IF((LEFT(TEXT($A6,"mm"),2))=((LEFT(TEXT('Wee kly Total -
2005'!$B$4:$B$123,"mm"),2))),'Weekly Total - 2005'!E$4:E$123,"-"))}


Can this be simplified ....


--
JustMe602
------------------------------------------------------------------------
JustMe602's Profile:
http://www.excelforum.com/member.php...o&userid=27854
View this thread: http://www.excelforum.com/showthread...hreadid=474003

Actually, all the formulas return zero in the absence of a match, so, use
the Sumproduct as a more efficient approach.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"RagDyer" wrote in message
...
One way, still keeping it as an *array* formula:

=SUM(IF(TEXT($A6,"mm")=TEXT('Weekly Total -
2005'!$B$4:$B$123,"mm"),'Weekly Total - 2005'!E$4:E$123,"-"))

If you could live with a zero instead of the dash ( - ), when no data
meets
the criteria, you could try this *regular*, *non-array* formula:

=SUMPRODUCT((TEXT($A6,"mm")=TEXT('Weekly Total -
2005'!$B$4:$B$123,"mm"))*('Weekly Total - 2005'!E$4:E$123))


"JustMe602" wrote
in message ...

{=SUM(IF((LEFT(TEXT($A6,"mm"),2))=((LEFT(TEXT('Wee kly Total -
2005'!$B$4:$B$123,"mm"),2))),'Weekly Total - 2005'!E$4:E$123,"-"))}


Can this be simplified ....


--
JustMe602
------------------------------------------------------------------------
JustMe602's Profile:
http://www.excelforum.com/member.php...o&userid=27854
View this thread:
http://www.excelforum.com/showthread...hreadid=474003

If you could live with a zero instead of the dash ( - ), when no data meets
the criteria

The use of the dash is superfluous.

Where the logical test is FALSE, the dashes are just TEXT elements in the
summed array:

=SUM({100;22;"-";50;"-"})

Since SUM ignores text, no harm done and if all elements of the array were
text, SUM = 0.

Biff

"RagDyer" wrote in message
...
One way, still keeping it as an *array* formula:

=SUM(IF(TEXT($A6,"mm")=TEXT('Weekly Total -
2005'!$B$4:$B$123,"mm"),'Weekly Total - 2005'!E$4:E$123,"-"))

If you could live with a zero instead of the dash ( - ), when no data
meets
the criteria, you could try this *regular*, *non-array* formula:

=SUMPRODUCT((TEXT($A6,"mm")=TEXT('Weekly Total -
2005'!$B$4:$B$123,"mm"))*('Weekly Total - 2005'!E$4:E$123))


"JustMe602" wrote
in message ...

{=SUM(IF((LEFT(TEXT($A6,"mm"),2))=((LEFT(TEXT('Wee kly Total -
2005'!$B$4:$B$123,"mm"),2))),'Weekly Total - 2005'!E$4:E$123,"-"))}


Can this be simplified ....


--
JustMe602
------------------------------------------------------------------------
JustMe602's Profile:
http://www.excelforum.com/member.php...o&userid=27854
View this thread:
http://www.excelforum.com/showthread...hreadid=474003