Refine SUMIF to include items within a date range

[rviners]

I have the following IF statement, which is looking Paul's sheet and where it states 'Renewal' or 'New' in the C column, it returns the values in column J...

=SUMIF(Paul!$C$4:$C$37,April!$B$1,Paul!$J$4:$J$37)

I now need this to look at a date range on Paul's sheet and only return the above data in that date range.

Is this possible?

Thank you
Rachel

[mehmetcik]

Welcome to the forum

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[6StringJazzer]

What column has the dates? What cells have the range of dates you want to include?

[rviners]

Data is on 'Ian!' sheet
So column B6:B500 on Ian! will have the date
Column J6:J500 has the income but it is dependent on the value in column C whether it shows as 'New' or 'Renewal' - I want to show separate results for New and Renewal on another sheet as a summary page month by month.

I currently have this formula that works looking at the total but I now need it to refer to a date range on the same sheet for Ian!

=SUMIF(Ian!$B$6:$B$500,April!$A$1(Ian!$C$6:$C$500,April!$B$1,Ian!$J$6:$J$500))

[rviners]

I think I have attached a sample spreadsheet...

[JeteMc]

To get separate sums of New and Renewal for the month of April for Ian through Mike C paste the following into cell B2, drag the fill handle down to cell B11 and then over to cell C11:

Please Login or Register  to view this content.

Let us know if you have any questions.

[rviners]

Thank you so much, this is great!

I thought I would be able to tweak this for the remaining columns but it doesn't seem to be working, as I've never used the SUMIFS or INDIRECT functions and don't understand what and how it's looking at the data.

On April! column D needs to show the variance shown on Ian! column K for 'Renewal' only which is shown in column C

Then on April! in column E I need to show the variance overall which includes both New and Renewal business.

Then on April! in column F I need to show the total income overall which is for New and Renewal business shown in column J

I assume this will work, when I copy the sheet and rename it May and repeat for June, July, August etc to show the separate in month summaries?

Thank you and look forward to receiving your advice

[JeteMc]

Please try the following:
For column D: =SUMIFS(INDIRECT($A2&"!K:K"),INDIRECT($A2&"!C:C"),C$1,INDIRECT($A2&"!B:B"),">="&$A$1,INDIRECT($A2&"!B:B"),"<="&EOMONTH($A$1,0))
For column E: =SUMIFS(INDIRECT($A2&"!K:K"),INDIRECT($A2&"!B:B"),">="&$A$1,INDIRECT($A2&"!B:B"),"<="&EOMONTH($A$1,0))
For column F: =SUMIFS(INDIRECT($A2&"!J:J"),INDIRECT($A2&"!B:B"),">="&$A$1,INDIRECT($A2&"!B:B"),"<="&EOMONTH($A$1,0))
The formulas will work for May, June etc. if the date for the first of each of those months is placed in cell A1 on the respective sheet. As INDIRECT is a volatile function which may slow the performance of the workbook, a more efficient way to do this would be to change the date on the existing (April) sheet.
Let us know if you have any questions.