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 negat...
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
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
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!
- Hi there
my data is set out like this
Tab 1
fee type 1 Dollar value
client 2
Client-1 2
fee type 2 Dollar value
client 2
client-1 3
fee type 3 Dollar value
client 2
client-1 3
tab 2 - summary tab
fee type Total Dollar value
client X
client -1. X
In tab 2, If I use sumif, but I need to exclude fee type 2 and 3 from the sumif function as that is not required . How would the formula look
could you please assist