|
|
|
|||||||
| Excel Questions All Excel/VBA questions - formulas, macros, pivot tables, general help, etc. Please post to this forum in English only. |
 |
|
|
Thread Tools | Display Modes |
Mar 12th, 2002, 09:16 AM
|
#1 |
|
Guest
Posts: n/a
|
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 |
|
|
Mar 12th, 2002, 09:17 AM
|
#2 |
|
Board Regular
Join Date: Feb 2002
Posts: 7,743
|
Sorry , that was my post above!
Brian |
|
|
|
Mar 12th, 2002, 09:31 AM
|
#3 |
|
New Member
Join Date: Mar 2002
Location: London, UK
Posts: 17
|
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 |
|
|
|
|
Mar 12th, 2002, 09:37 AM
|
#4 | |
|
MrExcel MVP
Join Date: Feb 2002
Location: The Hague
Posts: 50,319
|
Quote:
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 ] |
|
|
|
|
|
Mar 12th, 2002, 09:38 AM
|
#5 |
|
Board Regular
Join Date: Feb 2002
Posts: 7,743
|
Sorry it's a typo:
should be: sep.!$c$1:sep.!$c$360 |
|
|
|
|
Mar 12th, 2002, 09:43 AM
|
#6 |
|
Board Regular
Join Date: Feb 2002
Posts: 7,743
|
Re: Sep.
Yes it would! Let me try this without CSE. It's a rookie mistake, which I have been making a lot! Brian |
|
|
|
|
Mar 12th, 2002, 09:48 AM
|
#7 |
|
Board Regular
Join Date: Feb 2002
Posts: 7,743
|
Aloha!
Tried it and still doesn't pick up a day, still summing 72 instead of 80. Brian |
|
|
|
|
Mar 12th, 2002, 10:59 AM
|
#8 |
|
Board Regular
Join Date: Feb 2002
Posts: 7,743
|
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 |
|
|
|
|
Mar 12th, 2002, 11:49 AM
|
#9 | |
|
MrExcel MVP
Join Date: Feb 2002
Location: The Hague
Posts: 50,319
|
Quote:
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 |
|
|
|
|
|
| Bookmarks |
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
