Forum Discussion
Solved
Formula
Hi
I want to use sumifs as I multiple criteria
tab 1
Jan-23 <need total positive value >
jan-23 <need total negative value>
Feb-23 <need total positive values>
feb-23 <need total negative values>
tab 2
9/1/2023 -45
2/1/2023 46
1/2/2023 -45
1/2/2023 46
how do if use the sumifs function to summarise the data in tab 2 to reflect the summary in tab 1
please help
You can make the formula more flexible by using cell references instead of hard-coded dates. For example, you can enter the start and end dates for the desired month in two cells in Tab 1, let’s say cells C1 and D1. Then you can modify the formula to reference these cells instead of using hard-coded dates.
Here’s an example of how the modified formula for calculating the total positive values for the month specified in cells C1 and D1 would look like:
=SUMIFS(Tab2!B:B, Tab2!A:A, ">="&C1, Tab2!A:A, "<"&D1, Tab2!B:B, ">0")
Similarly, you can modify the formula for calculating the total negative values for the month specified in cells C1 and D1 as follows:
=SUMIFS(Tab2!B:B, Tab2!A:A, ">="&C1, Tab2!A:A, "<"&D1, Tab2!B:B, "<0")
With these modified formulas, you can easily change the month for which you want to calculate the total positive and negative values by simply changing the dates in cells C1 and D1.
I wish you a nice day and much success with Excel!
You can use the SUMIFS function in Excel to summarize the data in Tab 2 based on multiple criteria and display the results in Tab 1.
Here’s an example of how you can do this:
Let’s assume that your data in Tab 2 is organized as follows:
- Column A contains the dates
- Column B contains the values
In Tab 1, you can use the following formula to calculate the total positive values for January 2023:
=SUMIFS(Tab2!B:B, Tab2!A:A, ">="&DATE(2023,1,1), Tab2!A:A, "<"&DATE(2023,2,1), Tab2!B:B, ">0")
This formula sums the values in column B of Tab 2 where the corresponding date in column A is within January 2023 and the value in column B is greater than 0.
Similarly, you can use the following formula to calculate the total negative values for January 2023:
=SUMIFS(Tab2!B:B, Tab2!A:A, ">="&DATE(2023,1,1), Tab2!A:A, "<"&DATE(2023,2,1), Tab2!B:B, "<0")
This formula is similar to the previous one but it sums only the values that are less than 0.
You can use similar formulas to calculate the total positive and negative values for February 2023 or any other month. Just change the dates in the criteria to match the desired month.
I hope this helps!
- Hi Nic
Thank you so much, but can I use the arrayformula to covert column A , tab 2 convert to month- How could the formula look then. I don’t want to adjust the formula as the months and years change
