Forum Discussion
Need help on if statement that includes sumif
Going to do my best to explain this clearly. Currently i have a sumif statement with index match in it as well. I need to reformat it to have an if statement (or another function) that will check a cell for "a-" and do one thing and if it fails then do another Here's my current formula.
=SUMIFS(INDEX('GAS - Balance Sheet Detail - R'!$G:$FR,0,MATCH('2023 Current CP Graphs'!D1,'GAS - Balance Sheet Detail - R'!$G$1:$FR$1,0)),'GAS - Balance Sheet Detail - R'!$E:$E,'2023 Current CP Graphs'!A17,'GAS - Balance Sheet Detail - R'!$F:$F,'2023 Current CP Graphs'!$A$16,'GAS - Balance Sheet Detail - R'!$A:$A,'2023 Current CP Graphs'!B5)/1000000
This is working correctly, however i need to write another if statement. I need the formula to look at a cell and if that cell begins with "a-" then i need it to sum based on the criteria in the above formula. If the cell does not start with "a" then i need it to do my below formula.
=SUMIFS(INDEX('GAS - Balance Sheet Detail - R'!$G:$FR,0,MATCH('2023 Current CP Graphs'!G1,'GAS - Balance Sheet Detail - R'!$G$1:$FR$1,0)),'GAS - Balance Sheet Detail - R'!$E:$E,'2023 Current CP Graphs'!$B$17,'GAS - Balance Sheet Detail - R'!$F:$F,'2023 Current CP Graphs'!$A$16,'GAS - Balance Sheet Detail - R'!$A:$A,'2023 Current CP Graphs'!$B$5)/1000000
Thanks for the help!
What version of Excel do you currently have? If you're working with a subscription to Microsoft 365, then you would be well served by learning both the LET function and the LAMBDA function, using them to make these formulas shorter, and, thereby, more readable and maintainable.
Having said that, would it be possible for you to post a copy of the spreadsheet that contains these formulas; it's hard enough trying to decipher a formula that one has written one's self; deciphering one such as you've shown here, with its long and recurring table names (or are they tab names?), is even more difficult. So you could help us help you by posting a copy of the spreadsheet on OneDrive or GoogleDrive, with a link pasted here that grants edit access. That way we could actually test any new formulations with your real database.
