sum array
Posted by Ian on August 01, 2001 5:17 AM
Hi
can any one tell me what's wrong with this
{=SUM((C26:C45="permanent")*(D26:D45="csp")*(H26:H45<>0))}
column C: is status of someone's employment
column D: is job title
column H: is the values to add
the values are in time format and the return cell is set to Custom [h]:mm
the answer I'm getting is 144:00
which in general format is 6, which in turn is the number of the count() for the first 2 parts of the formula??? It seem to be ignoring the third part??
I could use
{=SUM(IF(C26:C45="permanent",IF(D26:D45="csp",H26:H45,0),0))}
and it works. I just wanted to find out how to make the other one work also.
Plus is there a reason to add () round the arrays:
e.g. {=sum(((A1:A10)="a")*(B1:B10)))
as apposed to sum((A1:A10="a")*(B1:B10)))
it seems to give the same result.
Thanks
Ian
Posted by Aladin Akyurek on August 01, 2001 5:53 AM
The term (H26:H45<>) is a boolean expression; Put differently, it just consists of logical values (TRUE,FALSE). Since you want add up times that are in H26:H45, use:
{=SUM((C26:C45="permanent")*(D26:D45="csp")*(H26:H45))}
If I may, the alternative with SUMPRODUCT would be:
=SUMPRODUCT((C26:C45="permanent")*(D26:D45="csp")*(H26:H45))
See above.
Yes, but contains nested IFs. If you'd have more conds, it can become difficult to process for the human designer & reader.
The 2nd is better. Even there, you can omit parens in the last term. The use of parens has the same function in the Excel formulas as in maths.
Of course.
Aladin
Posted by Mark W. on August 01, 2001 7:11 AM
Aladin's suggestion...
If you choose to use the SUMPRODUCT() function as
suggested by Aladin, I'd recommend that you change
it's arguments to be...
=SUMPRODUCT((C26:C45="permanent")+0,(D26:D45="csp")+0,H26:H45)
The reason I suggest this is that the description
of SUMPRODUCT in Excel Help states that this
function should have between 2 and 30 arguments.
The way it is presented by Aladin it only has 1.
My concern is that using a function in a manner
that's inconsistent with it's documentation could
leave you in the lurch if Microsoft modifies this
function and deprives you of this usage.
Posted by Ian on August 01, 2001 8:54 AM
sorry I should have explained
{=SUM((C26:C45="permanent")*(D26:D45="csp")*(H26:H45))}
returns a #VALUE error as it seems not to like an if formula returning "". also there're text strings in some of them because some people are "sick" etc.
re: the sumproduct, OF COURSE did we not go over this last night. BUT (and I haven't tested it) would this work given the restrictions I've pointed out.
Thanks again
Ian
Posted by Mark W. on August 01, 2001 9:06 AM
> also there're text strings in some of them
> because some people are "sick" etc.
Do I understand you to say that you have text
values mixed with time values in the same
column? Remember that advice I gave you last
night...
Posted by Ian on August 01, 2001 9:12 AM
>Remember that others may have to read and
understand your worksheet designs... so keep
them as straightforward as possible. Don't
mix data types in your data list columns.
If you have a column of IDs (and as such you'll
never perform math on them) store them as
text instead of numeric values.
All logged and noted.
I didn't set up the sheets, I'm lookins for quick fix for a different dept. I have my own data mixing to do :)
Also originally it was not required to do the calc. then, you know co.'s, it was.
Thanks
Ian
Posted by Aladin Akyurek on August 01, 2001 9:37 AM
If I understand you correctly, you have not only dates but also text entries in H26:H45 such as "sick" or "" (returned by an IF). The latter is the culprit. The all boolean array-formula can be modified to cope with it, the SUMPRODUCT formula cannot.
So the boolean array-formula becomes (as far as I see)
{=SUM((C26:C45 ="permanent")*(D26:D45 ="csp")*(IF(ISNUMBER(H26:H45),(H26:H45),0)))}
Given this result, I'd keep the original array-formula with nested IFs, that is:
=SUM(IF(C26:C45="permanent",IF(D26:D45="csp",H26:H45,0),0))
