Multiple Formulas

[McTam]

I wonder if someone can help me please. I am trying to set up a monthly accounts sheet which is taking information from other sheets. I am using SUMIF but I need to add another variant for a date range. Is there any way I can do this? The formula reads,
=SUMIF('Profit (10-11)'!A3:A101,TY!A14,'Profit (10-11)'!J3:J101)
So as you can see it is looking at a profit sheet and using a title (A14) for the search. Unfortunately this is a monthly sheet and the row across the top has the date range e.g. Jan 11. Ideally what I would like to do is search the date as well. Any ideas? I can always try to copy the sheet but it is quite big and I am not too sure whether the information would be clear?

Thank you in advance Alex

 

[schielrn]

So is column J one of the months? If so where do the months start, which column? And do you also need to take the year into consideration?

 

[VaraK]

Hi,

I would you use Sumifs in Excel 2007 and Sumproduct if using Excel 2003.

 

[schielrn]

Is this maybe what you are trying to do, this is just a simplistic example:

Excel Workbook
	I	J	K	L	M
1		Jan	Feb	Mar	Apr
2	rob	1	2	3	4
3	joe	4	3	2	1
4	joe		1
5
6			4
Sheet15

Cell Formulas
Range	Formula
K6	=SUMIF(I2:I5,"joe",OFFSET(J2:J5,0,MATCH("Feb",J1:M1,0)-1))

Hope that helps.

 

[McTam]

Thank you for your response, sorry for the delay I have been on a site so couldn't get access. I will try your formula but in answer to your first question normally the date range would start in Column B. Unfortunately I can't upload to the site as the HTML maker is not available. I have been asked to put this together by my MD and I think it is a bit above my pay grade (if you know what I mean). This will need to be updated every month and it is pulled from three other linked sheets. I have copied what I can into one sheet but obviously I would need to updated that monthly. Anyway thanks for your help,

Regards Alex