• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Calculations across columns vs. single column

G

gwstudent

New Member
Hi -


There is a great posting of a vacation dashboard here: http://chandoo.org/wp/2013/01/24/employee-vacations-tracker-dashboard/


This solution stores all of the vacation information in a single column, that is the vacation type, start date, and end date are in their own respective single column, but would the dashboard and calculations still work if the start dates, end dates, and vacation codes were in different columns for each vacation type?


The SUMIFS formula on the calculations worksheet goes down the list of vacation codes but if those vacations codes were in 4 separate columns what would be the correct method to use?
 
Gwstudent


Can you post a sample file with the layout your proposing?
 
Hi -


Yes. This spreadsheet is a mod of the vacation tracker that I have re-purposed. On the Tracking worksheet, Column P, row 60 there is a SUMIFS formula which works fine enough but it also needs to to check all the values in columns N and O (not just M) and populate the respective field with a 1, 2, or 3. I realize there is some overlapping with the dates - that's OK. Is there a workaround to the SUMIFS formula to check all 3 columns and not just column M?


https://docs.google.com/file/d/0B2V4Nkca2ASFNGhPZW8wRlc3cnc/edit?usp=sharing
 
Make the following changes:

M16: =IFERROR(MATCH($E$16:$E$182,Dakine,0),0)

N16: =IFERROR(MATCH($H$16:$H$182,Dakine,0),0)

O16: =IFERROR(MATCH($K$16:$K$182,Dakine,0),0)

Copy these 3 cells down


P16: =SUMPRODUCT((AllVC1+AllVC2+AllVC3)*($A$16:$A$182=$A16)*($C$16:$C$182<=P$15)*($D$16:$D$182>=P$15))

Copy across and down
 
Hui -


First of all that's genius and that works if I want to sum all of the columns but is there a way to get *exclusive* results? So, if the VC1 column is true then display 1 OR if VC2 column is true then display 2 OR if VC3 column is true then display 3? Again, I realize there are overlapping dates so if VC1 and VC2 are both true then take VC! as that occurs first.
 
I think this does it

P16:
Code: 
=IF(AllVC1>0,1,IF(AllVC2>0,2,if(Allvc3>0,3,0)))*SUMPRODUCT(($A$16:$A$182=$A16)*($C$16:$C$182<=Q$15)*($D$16:$D$182>=Q$15))
 
You must log in or register to reply here.
Back
Top