Formula to return value based on multiple criteria including specific date range

[ShellBeatz]

Hello,

I need to find a formula that will return a sum value (£) - it must look in Column A for a specific code that is on another worksheet e.g. IT300-100, sum any costs against that code (may be multiple lines of costs) between a specific date range e.g. Jan 2022 <31/01/2021 to >01/02/2022.

My first part of the code would usually be =SUMIFS('Forecast Spend'!,A6:A1000,A19,'Forecast Spend!',N6:N1000)

The date range would be another formula = =SUMIFS('Forecast Spend'!N6:N1035,'Forecast Spend'!C6:C1035,">31/12/2021",'Forecast Spend'!C6:C1035,"<01/02/2022")

But how can I combine the two?

Many thanks

[AliGW]

Administrative Note:

Welcome to the forum.

Members will tailor the solutions they offer to the version (NOT a release number like 2203) of Office (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.

[ShellBeatz]

Hi Ali,

Thank you for you help.

I have updated my Version - hope this is OK now

[AliGW]

Thanks.

Can you explain why the ranges in the two formulae are different? If you want to combine them into one formula (one SUMIFS?), you'll need to make them the same dimension.

It would help if you provided a sample workbook.

[ShellBeatz]

Hi Ali,

Yes I need to combine the two formulas. So it looks for a specific code on one tab and also in another column in a different tab (same code may be used multiple times in one column). Then bring back any costs against that code (different column) within a certain time range e.g. January (another column). I have attached a sample spreadsheet, so I'd need Tab1 populated by month - based upon criteria in Tab2. It should return £200 for Jan22 on Tab1. Thanks

[JeteMc]

Try pasting the following into cell C4 on the Tab1 sheet:

Please Login or Register  to view this content.

Once the formula is activated, then drag and fill handle over and down.
Let us know if you have any questions.

[ShellBeatz]

Hi JeteMc,

Thank you so much for your help, it works! You're a superstar!

[JeteMc]

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.