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

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

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

2. ## Re: Formula to return value based on multiple criteria including specific date range

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.

3. ## Re: Formula to return value based on multiple criteria including specific date range

Hi Ali,

Thank you for you help.

I have updated my Version - hope this is OK now

4. ## Re: Formula to return value based on multiple criteria including specific date range

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.

5. ## Re: Formula to return value based on multiple criteria including specific date range

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

6. ## Re: Formula to return value based on multiple criteria including specific date range

Try pasting the following into cell C4 on the Tab1 sheet:
Formula:
`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.

7. ## Re: Formula to return value based on multiple criteria including specific date range

Hi JeteMc,

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

8. ## Re: Formula to return value based on multiple criteria including specific date range

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.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1