Forum Discussion
Donna830
Jul 30, 2023Copper Contributor
Help please - Excel formula - SUM IF with AND and link to another sheet
I'm getting an Excel formula error and would appreciate help and guidance! Thanks in advance!!
(Screenshot below) In the sheet "Profit and Loss Statement," I'm trying to calculate the following: In the sheet "List of Transactions,", if "2200 Accounts Receivable" has been selected in cells F3 through F301 AND the value in the corresponding cell C3 through C301 is greater than 0, then add cells C3 through C301 and place that number in the sheet "Profit and Loss Statement."
See the screenshot below as an example. The sum in this case would be $30, as the negative numbers would be ignored.
Here's the formula I'm using that isn't working: =SUMIF(AND('List of Transactions'!F3:F301,"2200 Accounts Receivable",'List of Transactions'!C3:C301,”>0”,'List of Transactions'!C3:C301))
My guess is the comma between the two AND components is the issue, but I haven't found a different, acceptable way to express it.
=SUMIFS('List of Transactions'!C3:C301,'List of Transactions'!F3:F301,"2200 Accounts Receivable",'List of Transactions'!C3:C301,">"&0)
This formula returns the intended result in my sheet.
- OliverScheurichGold Contributor
=SUMIFS('List of Transactions'!C3:C301,'List of Transactions'!F3:F301,"2200 Accounts Receivable",'List of Transactions'!C3:C301,">"&0)
This formula returns the intended result in my sheet.
- Donna830Copper Contributor
OliverScheurich - All I can say is, "Wow!" The formula you provided works in my spreadsheet. Thank you SO much!!!