Excel Formula Help

Question

Hi I'd like to add a formula for my excel spreadsheet that calculates the ROW 20 to only show income when i select "settled - Self Gen" from the dropdown list in column G.

Currently Row 18 calculates both the "Settled" and "Settled Self Gen" but i need to separate the "Settled Self Gen"

These total ONLY show once an option has been selected in Column G and input a date in Column H.

The current formula for Row 13 - shows the following :

=SUMIFS(PAIGE!$S$24:$S$2417,PAIGE!$G$24:$G$2417,$A7,PAIGE!$H$24:$H$2417,">="&H$2,PAIGE!$H$24:$H$2417,"<"&I$2)+SUMIFS(PAIGE!$S$24:$S$2417,PAIGE!$G$24:$G$2417,$A8,PAIGE!$H$24:$H$2417,">="&H$2,PAIGE!$H$24:$H$2417,"<"&I$2)

The totals in ROWS 11/12/13/15/17 are all from totals input in columns Q/R/S/U/W

Screenshot below

Answer 1

Hello Paige SSS, Thanks for using Microsoft products and posting in the community.

Based on your description and the information in the screenshot, here is the formula you can use on line 20 to display the income when you select “Settled - Self Gen” in column G and enter the date in column H:

=SUMIFS(PAIGE!$S$24:$S$2417, PAIGE!$G$24:$G$2417, "settled - Self Gen", PAIGE!$H$24:$H$2417, ">="&H$2, PAIGE!$H$24:$H$2417, "<"&I$2) 

1. PAIGE!$S$24:$S$2417: This is the range of values you want to sum. In this case, it’s the values in column S of the PAIGE sheet, from row 24 to row 2417.
2. PAIGE!$G$24:$G$2417, "settled - Self Gen": This is the first condition. It specifies that only the rows where column G has the value “settled - Self Gen” will be included in the sum.
3. PAIGE!$H$24:$H$2417, ">="&H$2: This is the second condition. It specifies that only the rows where the date in column H is greater than or equal to the date in cell H2 will be included.
4. PAIGE!$H$24:$H$2417, "<"&I$2: This is the third condition. It specifies that only the rows where the date in column H is less than the date in cell I2 will be included.

I hope this is what you want. Best Regards,Arthur - MSFT | Microsoft Community Support Specialist

Hi - Thank you for your reply - i also need it to calculate the sums in Columns R / U / W

Your above forula worked for the Sums in Column S - so i just need to add the other column totals too please :)

Answer 2

Hello Paige SSS, Thanks for using Microsoft products and posting in the community.

Based on your description and the information in the screenshot, here is the formula you can use on line 20 to display the income when you select “Settled - Self Gen” in column G and enter the date in column H:

=SUMIFS(PAIGE!$S$24:$S$2417, PAIGE!$G$24:$G$2417, "settled - Self Gen", PAIGE!$H$24:$H$2417, ">="&H$2, PAIGE!$H$24:$H$2417, "<"&I$2) 

1. PAIGE!$S$24:$S$2417: This is the range of values you want to sum. In this case, it’s the values in column S of the PAIGE sheet, from row 24 to row 2417.
2. PAIGE!$G$24:$G$2417, "settled - Self Gen": This is the first condition. It specifies that only the rows where column G has the value “settled - Self Gen” will be included in the sum.
3. PAIGE!$H$24:$H$2417, ">="&H$2: This is the second condition. It specifies that only the rows where the date in column H is greater than or equal to the date in cell H2 will be included.
4. PAIGE!$H$24:$H$2417, "<"&I$2: This is the third condition. It specifies that only the rows where the date in column H is less than the date in cell I2 will be included.

I hope this is what you want. Best Regards,Arthur - MSFT | Microsoft Community Support Specialist