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!
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 assistAssuming the fee types are listed in column A, the dollar values are listed in column B, and the client names are listed in column C, you can use the SUMIFS function in tab 2 to exclude fee type 2 and 3 from the total.
Here's how the formula would look like:
=SUMIFS('Tab 1'!B:B,'Tab 1'!C:C,C2,'Tab 1'!A:A,"Fee type 1")
In this formula, C2 is the cell that contains the client name in tab 2, and "Fee type 1" is the fee type that you want to include in the total. You can modify the formula to include additional fee types as needed.
This formula will only sum the dollar values for fee type 1 and for the specific client name in cell C2. The SUMIFS function allows you to specify multiple criteria to filter the data, so you can exclude fee types 2 and 3 by simply not including them in the formula.
One alternative formula would be to use the SUMPRODUCT function with two conditions:
=SUMPRODUCT(('Tab 1'!C:C=C2)*('Tab 1'!A:A="Fee type 1"),'Tab 1'!B:B)
In this formula, 'Tab 1'!C:C=C2 checks if the client name in 'Tab 1'!C:C matches the client name in cell C2, and 'Tab 1'!A:A="Fee type 1" checks if the fee type in 'Tab 1'!A:A matches "Fee type 1". The * operator multiplies the two conditions together, resulting in an array of 1's and 0's, where 1 represents a row that meets both conditions.
Finally, the SUMPRODUCT function multiplies this array with the dollar values in 'Tab 1'!B:B, and returns the sum of the products, effectively summing only the dollar values for fee type 1 and for the specific client name in cell C2.
Note that this formula assumes that all the data in 'Tab 1'!A:B is numeric, and there are no blank cells or text values. If there are text values or blanks in the data, you may need to modify the formula with additional conditions to exclude those rows.