SUMIFS - Not Working as I would like

I am trying to use the SUMIFS function for the first time in Excel 2013.



=SUMIFS(Sheet1!D3:D10,Sheet1!F3:F10,"=>Sheet2!B3",Sheet1!G3:G10,"<Sheet2!C3")




SUMIFS.png




Sum_range - This currently has 2 figures in, £75 and £7152. It associates to monthly income.

Criteria_range1 - This is the start date as to when the monthly income starts. It has 2 dates 01/05/2013 & 13/09/2013

Criteria1 - This points at a cell with the date 01/05/2013

Criteria_range2 - This is the date when the monthly income ends. It has 2 dates in so far, 01/05/2010 & 14/09/2013.




I would expect that in May 2013 it would calculate £75 and in September 2013 £75 + £7152. I have played around with the syntax but the result always ends in £0.00. What am I doing wrong?




Thank you in advance for your help.

Book2.xlsxBook2.xlsx

Originally Posted by benishiryo

welcome to the forum, Chris.Wiliams. minor change:
=SUMIFS(Sheet1!D3:D10,Sheet1!F3:F10,">="&Sheet2!B3,Sheet1!G3:G10,"<"&Sheet2!C3)

the operators (>=, <) should be separated from the cell references (Sheet2!B3, Sheet2!C3)
otherwise, it will mean that you are searching for a text that looks like this:
=>Sheet2!B3

the other thing is that the greater than sign or less than sign should be before the equals sign. if it doesn't work, I suggest you upload an Excel file in the thread. To do that, Click on GO ADVANCED and use the paperclip icon to open the upload window.
View Pic
and to illustrate your example better, click on How To Get Quick & Good Answers

Edit:
@AndyPope
think you overlooked this part:
=SUMIFS(Sheet1!D3:D10,Sheet1!F3:F10,"=>" & Sheet2!B3",Sheet1!G3:G10,"<" & Sheet2!C3")


and also, Chris:
http://answers.microsoft.com/en-us/o...5-2d1d54c89511
do take note about cross-posting
Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Thank you very much for your help. I am now getting figures in the selected cells, but not the correct figures that I am looking for.

Sheet 1 - This is where I am starting to list our customers and the jobs that we are doing for them. Some jobs are 1 time jobs, others are invoiced monthly. Customer 2 Had a 1 time job for a new server and their monthly support was reduced by £35 at the end of May 2013. All this info is entered manually.

Sheet 2 - I want sheet 2 to be fully automated as I make amendments to sheet1. I want the revenue column (Sheet 2 D:D) to be able to look at sheet 1 and include the revenue for all jobs (Sheet1 D:D) which took place during the month. So cell (Sheet2 D3)should include all revenue in column (Sheet1 D:D) as long as 01/05/2013 falls in between the start date and end date in columns (Sheet1 F:F) and (Sheet1 G:G). Once I have got the correct SUMIFS function for cell D3 on Sheet 2 I am pretty confident that I will be able to complete the workbook comfortably.

I hope I have explained it clearly enough, thanks in advance for your help.

Regards,

Chris

Thanks guys, that seems to have done the trick. Much appreciated, my apologies for crossposting without reference

Originally Posted by Andy Pope

=SUMIFS(Sheet1!$D$3:$D$999,Sheet1!$F$3:$F$999,"<="&Sheet2!C3,Sheet1!$G$3:$G$999,">="&Sheet2!B3)

Brilliant. Your a star

Many thanks