Sheet Summary with 2 conditions including date range

Drea Mora

Hello,

I'm attempting to write a summary sheet formula that sums a number based on two conditions: 1 for entries <1/1/2021 and another column with a drop down choice of categories for only one category. Any luck writing a formula for a sum of two conditions? Should I be using SUMIF? How do I format both the conditions in the formula?

Heather Duff

Hi @Drea Mora ,

You'll want to use a SUMIFS formula:

=SUMIFS([column to sum]:[column to sum],[date column]:[date column],<DATE(2021,1,1),[drop down column]:[drop down column],"dropdown value")

Note - this will work for a single dropdown column. If you have a multi-select dropdown, let me know and we can adjust it.

Let me know if it works for you.

Best,

Heather

Heather Duff

Hi @Drea Mora ,

You'll want to use a SUMIFS formula:

=SUMIFS([column to sum]:[column to sum],[date column]:[date column],<DATE(2021,1,1),[drop down column]:[drop down column],"dropdown value")

Note - this will work for a single dropdown column. If you have a multi-select dropdown, let me know and we can adjust it.

Let me know if it works for you.

Best,

Heather

Drea Mora

@Heather D that did it thank you so much!

Drea Mora

Heather Duff

@Drea Mora Happy to help! :)

Chloe MB

Hello @Heather Duff

I am having a similar issue to Drea and wondering if you could help me as formulas are not my strong point.

I want a sheet summary to show me the following SUM,

Total Pay figure, (of data between 01/02/23 - 31/01/24), and Status to be Reconciled.

I am hoping you can help me with this, I look forward to your reply.

Best wishes,

Chloe

Genevieve P.

Hey @Chloe MB

You can adjust the formula above for what you're looking to do, like so:

=SUMIFS([Total Pay figure]:[Total Pay figure], [date column]:[date column], >=DATE(2023, 02, 01), [date column]:[date column], <=DATE(2024, 01, 31), Status:Status,"Reconciled")

Cheers,

Genevieve

Chloe MB

Hey @Genevieve P.

Thank you so much for this. I have copied the formula however my sum is equalling 0 when it should show a £ figure. Have I missed something?

Thank you in advance!

Best wishes,

Chloe

Andrée Starå

Hi @Chloe MB

I hope you're well and safe!

You'll have to set the field to GBP currency. (click on the three dots)

Did that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Chloe MB

Hello @Andrée Starå

Thank you for this! I am still having issues and am stumped.

I created a report with the same filters as the formula in the sheet summary, but the report produces the correct number where the formula is still showing 0 in the summary.

Can you see where I am making a mistake?

Best wishes,

Chloe

Andrée Starå

@Chloe MB

Happy to help!

Can you share some screenshots of the sheet? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help.

Genevieve P.

Hi @Chloe MB

I see in your formula you're looking at [Pay Date] but in your Report you're looking at [Invoice Date]... can you double check the columns you're referencing to ensure they're the correct ones?

Chloe MB

Hey, @Genevieve P.

Yes! This was the problem. Sorry, I missed this but thank you so much for your help.

The formula is now working!

Best wishes,

Chloe