• 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.

Find if dates in range and add associated number on another tab

T

theath

New Member
The "Data" tab in my spreadsheet contains a list of dates in column J (format is date, example: 1/27/2012).

On the "Monthly Hours" tab column A contains each month, column B contains the workable hours in that month.


........A........B

1....Jan......176

2....Feb......168

3....Mar......176

4....Apr......178

5....May......184

etc.


I need to look at column J in the "Data" tab and for each different month in that column, I need to find the value in "Monthly Hours" column B that is associated with that month. Each of these values must be added together. (each month counted only once regardless of how many times it shows up in column.)


The goal being that the formula calculates the total working hours for the months listed in column J on the data tab.
 
Hi, theath!


Assuming you can create a helper column in Data sheet, let's say column K, and that your hours in that sheet are in column E, try this:

a) in Data sheet, type in cell K2 (if has titles) and copy down as needed:

=ELEGIR(MES(J2);"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dic") -----> in english: =CHOOSE(MONTH(J2),"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dic")

b) in Monthly Hours sheet, type in cell C1 and copy down as needed:

=SUMAR.SI(Data!K:K;A1;Data!E:E) -----> in english: =SUMIF(Data!K:K,A1,Data!E:E)


Regards!
 
Unfortunately I am unable to create the helper column.


What I forgot to mention is that in the Monthly Hours sheet, column A is the first day of the month (1/1/12, 2/1/12, etc). I have it formatted as MMMM. After thinking about this longer, I really need to identify the Monthly hours associated with a month and year combination. For each month and year listed in "Data" column J - I need to find and sum the associated hours in "Monthly Hours" column B.
 
I have been trying to make the suggested formula work by adding a helper column to the Monthly Hours sheet, and just pointing it to the data sheet. The number of rows in the Data sheet will vary so I have to copy the formula down far enough to accomodate fluctuations in the number of rows. When it gets to a blank row, it returns January as the result. This would result in January's number being added, even if there are no January dates on the Data tab. :(
 
Hi ,


Check the following link :


http://chandoo.org/forums/topic/posting-a-sample-workbook


In brief , just upload your workbook to any file-sharing website ( Rapidshare , Skydrive , Google Docs , Speedy ,... ) , give file access to others , and post the access link here.


Narayan
 
That was easy enough :) I have uploaded the workbook to:

https://docs.google.com/open?id=0BxhVKid9p8IBZzZFM2pFSUZTdUMydjljOXdCZ0QwUQ
 
OK, so I just got a little bit further. I have this working for months. Now I just need to add matching for both the month and year. Uploaded a new spreadsheet.


https://docs.google.com/open?id=0BxhVKid9p8IBYXpqU1JuWHZUY2FOa2xlZ0tlOXJfZw
 
Can I easily modify this formula to also look at the year?


=IF(SUMPRODUCT(--(MONTH(Table1[Date])=1))>0,B2,0)
 
You must log in or register to reply here.
Back
Top