Can I replace repeating portions of formula with a Function? 1

[Divercem]

I have the following spreadsheet, that I need to draw calculations from and the formula that is too long follows that which is used to calculate days of the week based on a value of Monday through Friday, but I keep repeating the same statements trying to keep from running COUNTIF's and SUMIF's on all of the rows in the spreadsheet. The formula does a MATCH to find the first occurance of each year (2005 starts at Row 158) so I can only sum (or count) those rows that correspond with the year being calculated. I've been trying to figure out how to incorporate a function in place of the portions that keep repeating.

I'm sure there is a better way, I'm just not experienced enough to find it.

I have a similar formula for weekend calculations that works fine and I suppose I could just subtract the weekend values to get what I need.

Any help is appreciated.

Thanks,

Diver

A B C D E F
1 Year Date Day of Week In Time Out Time Hrs
2 2004 5/14/2004 Friday 7:30 AM 4:15 PM 8:15
3 2004 5/17/2004 Monday 7:30 AM 1:30 PM 6:00
4 2004 5/18/2004 Tuesday 8:00 AM 5:00 PM 8:30
5 2004 5/19/2004 Wednesday 8:12 AM 5:40 PM 8:58
6 2004 5/20/2004 Thursday 7:58 AM 4:45 PM 8:17
7 2004 5/21/2004 Friday 7:52 AM 4:45 PM 8:23
8 2004 5/24/2004 Monday 7:15 AM 4:30 PM 8:45
9 2004 5/25/2004 Tuesday 7:47 AM 4:30 PM 8:13
10 2004 5/26/2004 Wednesday 7:47 AM 5:35 PM 9:18

I J K L N O
2 Start Time
3 Normal Hrs 8:00
(4-6 Blank)
7 2004 2005 2006 2007
8 Before 8am 629 134 177 202 115
9 Exactly 8am 56 3 23 14 15
10 After 8 am 128 19 51 35 23

=(Sumif(INDIRECT("C" & MATCH(K$7,$A:$A,0) &":C"&IF(ISNA(MATCH(L$7,$A:$A,0)),$J$15-1,MATCH(L$7,$A:$A,0)-1)),”Monday”, INDIRECT("F" & MATCH(K$7,$A:$A,0) &":F"&IF(ISNA(MATCH(L$7,$A:$A,0)),$J$15-1,MATCH(L$7,$A:$A,0)-1))+Sumif(INDIRECT("C" & MATCH(K$7,$A:$A,0) &":C"&IF(ISNA(MATCH(L$7,$A:$A,0)),$J$15-1,MATCH(L$7,$A:$A,0)-1)),”Tuesday”, INDIRECT("F" & MATCH(K$7,$A:$A,0) &":F"&IF(ISNA(MATCH(L$7,$A:$A,0)),$J$15-1,MATCH(L$7,$A:$A,0)-1))+Sumif(INDIRECT("C" & MATCH(K$7,$A:$A,0) &":C"&IF(ISNA(MATCH(L$7,$A:$A,0)),$J$15-1,MATCH(L$7,$A:$A,0)-1)),”Wednesday”, INDIRECT("F" & MATCH(K$7,$A:$A,0) &":F"&IF(ISNA(MATCH(L$7,$A:$A,0)),$J$15-1,MATCH(L$7,$A:$A,0)-1))+Sumif(INDIRECT("C" & MATCH(K$7,$A:$A,0) &":C"&IF(ISNA(MATCH(L$7,$A:$A,0)),$J$15-1,MATCH(L$7,$A:$A,0)-1)),”Thursday”, INDIRECT("F" & MATCH(K$7,$A:$A,0) &":F"&IF(ISNA(MATCH(L$7,$A:$A,0)),$J$15-1,MATCH(L$7,$A:$A,0)-1))+Sumif(INDIRECT("C" & MATCH(K$7,$A:$A,0) &":C"&IF(ISNA(MATCH(L$7,$A:$A,0)),$J$15-1,MATCH(L$7,$A:$A,0)-1)),”Friday”, INDIRECT("F" & MATCH(K$7,$A:$A,0) &":F"&IF(ISNA(MATCH(L$7,$A:$A,0)),$J$15-1,MATCH(L$7,$A:$A,0)-1))+)/$J16

 

[AnotherHiggins]

If I understand your goals correctly, Youcould use a SumProduct function. This will allow you to add all cells in a column for records (rows) where the year match your target.

In your example above*, assuming you are counting how many Start Times were before 8 AM, and assuming that you have 8 AM in cell L3 (sorry, but it is hard to tell from the misaligned table) you would use this formula in J8:
[COLOR=blue white]=SumProduct((A2:A10000=J$7)*(D210000<L3))[/color]

That would count how many start times were before 8AM in 2004.

Also consider using a Pivot Table. That would be a very quick way to get a summary without having this messy stuff on the same sheet as your data.

BTW, for future reference, you can use the [ignore][tt][/tt][/ignore] tags to make tables line up like they do in the submit box.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[Divercem]

John thanks for the information and the tip on aligning the tables. Your solution works great for those fields that compare the Year and 1 other criteria.

Apparently, I was putting too much emphasis on reading only the rows for the years I am calculating instead of just reading 10000 rows...that is what was making my formula too long.

I really like your solution to use SumProduct(), I didn't understand how to use it before but I have been experimenting with it and can see its value. It is a great solution for matching 2 criteria. Part of what I need is to separate Weekdays from Weekends. I played with trying to get the SumProduct() to incorporate WEEKDAY() & CHOOSE() functions so I wouldn't have to check for each weekday, but kept coming up with Value# error. So I added each weekday and came up with the formula below to calculate the just the weekdays.


=SUMPRODUCT((A2:A10000=K7)*(C2:C10000="Monday"))+SUMPRODUCT((A2:A10000=K7)*
(C2:C10000="Tuesday"))+SUMPRODUCT((A2:A10000=K7)*(C2:C10000="Wednesday"))+
SUMPRODUCT((A2:A10000=K7)*(C2:C10000="Thursday"))+SUMPRODUCT((A2:A10000=K7)
*(C2:C10000="Friday"))

If I can shorten it I would like to know how, but this works.

Thanks for your time and the tips

 

[AnotherHiggins]

Sorry I didn't do anything to explain how SumProduct works in my last post. It was after midnight here and I was a bit tired. Here is a page that does a great job of explaining how to use Sum Product:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html#classic

As for shortening your formula, a couple of things....

First, you can use more than two criteria in SumProducts.

Also, you can shorten it to only looking at two days: Saturday and Sunday.
- For weekdays, only look at items where the weekday is NOT Sat or Sun
- For weekends, do two SumProducts. One each looking only at items where the weekday is Sat or Sun.

As it happens, something similar was covered in another thread. You might find it interesting/useful: thread68-1397057

Let's say you still want to look at only times before 8AM, and you still have 8AM entered into cell L3. You could use the following to count incidents in 2004 on a weekday where the start time was before 8AM:
[COLOR=blue white]=SUMPRODUCT((A2:A10000 = K7) * (C2:C10000 <> "Monday") * (C2:C10000 <> "Tuesday") * (D210000 < L3))[/color]

As you can see, each little part of the SumProduct defines another criteria.

As I mentioned above, I'd just do as you did above and add the results of two different SumProducts to get the weekend days.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[Divercem]

John,

Thanks for the reference. A lot of info there and I still haven't fully grasped all the concepts, but it is a great reference.

On that reference page I found another solution that is works too--

=SUMPRODUCT(--(A2:A10000=K7)*--(C2:C10000={"Monday","Tuesday","Wednesday","Thursday","Friday"}))

I also found some code to leave out holidays if they ever ask me to produce that number.

)