Thanks:  0
Likes:  0

# Thread: Array formula not computing

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

2. Sorry , that was my post above!

Brian

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

4. 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?

[ This Message was edited by: Aladin Akyurek on 2002-03-12 08:40 ]

5. Sorry it's a typo:

should be:

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

6. Re: Sep.

Yes it would! Let me try this without CSE.

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

Brian

7. Aloha!

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

Brian

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

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

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•