Simplify syntax of a Sum(IF... Array

J

JustMe602

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


Can this be simplified ....
 
M

Myrna Larson

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.
 
R

RagDyer

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))
 
R

RagDyer

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 said:
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))
Click to expand...
 
B

Biff

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

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
 
Top