Array formula not computing

[Guest]

Aloha!

I have this array formula:

=sumproduct((sep.!$b$1:sep.!$b$360="employee name")*(sep.!$d$1:sep.!$d$360="vac"),sep.!$c$1:sep.!$c$1:sep.!$c$360)+sumproduct(sep.!$g$1:sep.!$g$360="employee name")*(sep.!$i$1:sep.!$i$360="vac"),sep.!$h$1:sep.!$h$360)

It's not summing correctly, when it should be 80 it's summing 72, missing a day. I've been trying to figure this out to no avail. Anybody has any ideas. Cells are formatted correctly.

Brian

 

[Brian from Maui]

Sorry , that was my post above!

Brian

 

[RaoulF]

Brian

I haven't tried your formula, but looking at it the following looks a bit odd, is it meant to include the $C$1 twice?

$c$1:$c$1:$c$360

Raoul

 

[Aladin Akyurek]

On 2002-03-12 08:16, Anonymous wrote:
Aloha!

I have this array formula:

=sumproduct((sep.!$b$1:sep.!$b$360="employee name")*(sep.!$d$1:sep.!$d$360="vac"),sep.!$c$1:sep.!$c$1:sep.!$c$360)+sumproduct(sep.!$g$1:sep.!$g$360="employee name")*(sep.!$i$1:sep.!$i$360="vac"),sep.!$h$1:sep.!$h$360)

It's not summing correctly, when it should be 80 it's summing 72, missing a day. I've been trying to figure this out to no avail. Anybody has any ideas. Cells are formatted correctly.

Brian

=sumproduct((sep.!$b$1:$b$360="employee name")*(sep.!$d$1:$d$360="vac"),sep.!$c$1:$c$360)+sumproduct(sep.!$g$1:$g$360="employee name")*(sep.!$i$1:$i$360="vac"),sep.!$h$1:$h$360)

as Raoul observes and you don't need and shouldn't array-enter this formula.

By the way, wouldn't "sep" instead of "sep." do as sheet name?

Aladin
This message was edited by Aladin Akyurek on 2002-03-12 08:40

 

[Brian from Maui]

Sorry it's a typo:

should be:

sep.!$c$1:sep.!$c$360

 

[Brian from Maui]

Re: Sep.

Yes it would! Let me try this without CSE.

It's a rookie mistake, which I have been making a lot!

Brian

 

[Brian from Maui]

Aloha!

Tried it and still doesn't pick up a day, still summing 72 instead of 80.

Brian

 

[Brian from Maui]

Aloha!

I found the error in the sheet that had the data. I deleted rows then added the rows back. Then the formula summed the values correctly. Anyone know why that's so?

Brian

 

[Aladin Akyurek]

On 2002-03-12 09:59, Brian from Maui wrote:
Aloha!

I found the error in the sheet that had the data. I deleted rows then added the rows back. Then the formula summed the values correctly. Anyone know why that's so?

Brian

Brian,

Here is the modified SUMPRODUCT formula that you can use:

=SUMPRODUCT((MAR!$B$4:$B$360=$B3)*(MAR!$D$4:$D$360="VAC"),MAR!$C$4:$C$360)+SUMPRODUCT((MAR!$G$4:$G$360=$B3)*(MAR!$I$4:$I$360="VAC"),MAR!$H$4:$H$360)

As you can see I modified the sheet names with periods in them into ones without. The former clutters in my opinion the formula.

I created a hidden column B, which now contains the names whose vacation hours are totaled.

I did a few checks on why the total 72 instead of 80 for Garcia. Didn't find anything like an extra space. The "time" entries appear all numeric. So I'm curious which row on you MAY sheet is in error in the WB you sent me.

I'm resending the WB back, where you can replace the erroneous record/row with the right one. I'd urge to use this WB. & don't forget, will you: it's very seldom the case that the SUMPRODUCT formulas are array-entered.

Aladin