Forum Discussion

Donna830's avatar
Donna830
Copper Contributor
Jul 30, 2023
Solved

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.

 

  • Donna830 

    =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.

  • Donna830 

    =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.

    • Donna830's avatar
      Donna830
      Copper Contributor

      OliverScheurich - All I can say is, "Wow!" The formula you provided works in my spreadsheet. Thank you SO much!!!

Resources