I have attached a sample of a worksheet I have been working on. It is a time sheet template.I need to be able to sum all values associated with the lunch break in a cell. I am using index match to detect when Y is listed in the D column and putting the corresponding value in cell E21. If no Y is found and cell E20 is greater than 6, it defaults to .50
How do i add the appropriate sum function within the index match formula?
Skip the INDEX(..MATCH, it's best left strictly for returning string values. For calculation, I'd stick with SUMPRODUCT and SUMIF.
=if(e20<6,0,if(sumif(d$4:d$19,"y",e$4:e$19)=0,0.5,sumif(d$4:d$19,"y",e$4:e$19)))
Make Mom proud: Add to my reputation if I helped out!
Make the Moderators happy: Mark the Thread as Solved if your question was answered!
This does put the default .50 in the cell but when I put a y in the d column it doesn't change the value to match.
Start End Lunch Total time
8:00 AM 8:45 AM 0.75
8:45 AM 10:30 AM 1.75
10:30 AM 12:30 PM 2.00
12:30 PM 1:15 PM 0.75
1:15 PM 3:30 PM Y 2.25
7.50 total time
0.50 lunch total (this needs to equal the value in e that corresponds to the y in D)This should be 2.25 not .5 and if the y was in multiple rows it would need to add all the e column values that matches the d row with a y in it.
Last edited by tlstevens; 05-08-2015 at 02:38 PM.
I made a mistake when copying the formula and it changed column references. This works perfectly!!!
Thanks!!!!!!!!!!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks