If(and(sumifs formula

[mysticmoron109]

Hi All,

Not sure how to make a nice nested formula to pull this. I can do it by splitting out the various months and running a formula in each cell and targetting the dates that way but the spreadsheet is already very heavy (200 MB) so looking for a way to create a neat nested formula.

The sumifs formula is no problem, its just various criteria I got that no issues.

However, I want the sumifs to run for a particular scenario.

Here goes:

If the start date is <= December 31 2017 and the end date is => December 31 2017, then run the sumifs formula.

The problem is there is approximately 200,000 rows with start and end dates and I need to put the result in a single cell.

For reference start date is column D, end date is column E.

Hopefully this makes sense?

Much thanks in advance!

[63falcondude]

Assuming that the column to sum is column A, try this:

=SUMIFS(A:A,D:D,"<="&DATE(2017,12,31),E:E,">="&DATE(2017,12,31))

[mysticmoron109]

Assuming that the column to sum is column A, try this:

=SUMIFS(A:A,D:D,"<="&DATE(2017,12,31),E:E,">="&DATE(2017,12,31))

Thank you very much for speedy response, I will try that with the various dates.

But the dates are listed in a table already, say in E2, E3, E4, etc ... is there any way to run the formula by targeting those cells instead of manually typing in the date?

[63falcondude]

Absolutely, just use the cell references instead of the manually entered date.

Like this:

=SUMIFS(A:A,D:D,"<="&E2,E:E,">="&E3)

Where your start date is in cell E2 and your end date is in cell E3.

[mysticmoron109]

Absolutely, just use the cell references instead of the manually entered date.

Like this:

=SUMIFS(A:A,D:D,"<="&E2,E:E,">="&E3)

Where your start date is in cell E2 and your end date is in cell E3.

Sorry i think im confusing you a bit with my wording...

The start date and end date is in a separate sheet, in columns D and Columns E. The target dates, i.e., start date <= "date" and end date =>"date", are listed in a separate sheet in cells E72, E73, E74, E75, E76 etc for 13 month ends.

[63falcondude]

You will be best off creating a small representative sample workbook that includes the desired results of the formula (which you can type in manually).

To upload an Excel workbook, follow these steps:
1) Click on "Go Advanced"
2) Click on "Manage Attachments"
3) Click on "Choose File"
4) Choose your file and click on "Open"
5) Click on "Upload"
6) Click on "Close this window"

[mysticmoron109]

Attachment uploaded.

The target dates for the start/end date to be greater to/less than are the 2017 dates.

[63falcondude]

What cell in the sample from post #7 is your desired result in?

[mysticmoron109]

Cells F11:F23. I can then extrapolate the results across G11:O23 based on the formula solution.

The target dates for which the start and end dates in the data should should be <= & => are listed in E11:E23

Thanks again!

[63falcondude]

Understood. Just so that I don't have to look through all 219 columns, what column (in the 'Data' worksheet) should be summed for Commercial Renew (column F in the 'Table' worksheet)?

Better yet, how do you get 4441250 for F11? What cells (in the 'Data' worksheet) add up to this value?

[mysticmoron109]

Column BC is the target column for the sum. The 4441250 was just a number i snapped from previous workings... it inst a representation of the data I have provided you with.

[63falcondude]

it inst a representation of the data I have provided you with

Then I can't test it on my end to check that the result is what you were expecting.
That's why post #6 asked for a small representative sample. The desired outcome in your sample should still make sense based on the sample data, just in a much smaller scale.

Also, there is no such thing as "=>". I am assuming that you meant greater than or equal to, which is written as ">=".

Try this (below). I am still guessing at what it is that you want.

F11 =SUMIFS(Data!BC:BC,Data!D:D,"<="&E11,Data!E:E,">="&E11)

This formula takes the sum of column BC (of the 'Data' sheet) when the date in column D (of the 'Data' sheet) is ≤ the date in E11 (of the 'Table' sheet) and when the date in column E (of the 'Data' sheet) is ≥ the date in E11 (of the 'Table' sheet).

[mysticmoron109]

Then I can't test it on my end to check that the result is what you were expecting.
That's why post #6 asked for a small representative sample. The desired outcome in your sample should still make sense based on the sample data, just in a much smaller scale.

Also, there is no such thing as "=>". I am assuming that you meant greater than or equal to, which is written as ">=".

Try this (below). I am still guessing at what it is that you want.

F11 =SUMIFS(Data!BC:BC,Data!D:D,"<="&E11,Data!E:E,">="&E11)

This formula takes the sum of column BC (of the 'Data' sheet) when the date in column D (of the 'Data' sheet) is ≤ the date in E11 (of the 'Table' sheet) and when the date in column E (of the 'Data' sheet) is ≥ the date in E11 (of the 'Table' sheet).

This doesn't work. Bizarrely enough, it returns the same result as if I do not include the additional ≥ ≤ criteria.

[mysticmoron109]

This doesn't work. Bizarrely enough, it returns the same result as if I do not include the additional ≥ ≤ criteria.

and even when I use the manual date function i.e., Date(2017,1,31) and change it to say (2017,3,31) the result stays the same

[mysticmoron109]

Ah! I think I've got it.... silly enough I think I left in a criteria which contradicted it in the sumifs.... now that I have removed the previous condition it seems to work! Thanks Mate... just let me double check and mark as solved if good to go.

[finalazy]

Just curious, in your data sheet policy wording column G and issue as column H, should it be the same name as your Table sheet row 10? something like Commerical Renewal, Commerical Endorsement instead of the current Commerical Renew, Commerical New

[mysticmoron109]

Works like a charm, thank you so much mate! Very much appreciated!

[mysticmoron109]

Just curious, in your data sheet policy wording column G and issue as column H, should it be the same name as your Table sheet row 10? something like Commerical Renewal, Commerical Endorsement instead of the current Commerical Renew, Commerical New

No, all other criteria was fine. The problem was I left in a previous criteria that overruled (I suppose) the <= >= criteria. It works perfectly as 63falcondude solution.

[63falcondude]

Great, glad we could get you to a working solution.

Thank you for the rep and for marking this thread as SOLVED.