Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Array formula not computing

  1. #1
    Guest

    Default

    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. #2
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,450
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sorry , that was my post above!

    Brian

  3. #3
    New Member
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,640
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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 ]

  5. #5
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,450
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sorry it's a typo:

    should be:

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

  6. #6
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,450
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #7
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,450
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Aloha!

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

    Brian

  8. #8
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,450
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #9
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,640
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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