Sumif Based on Multiple Criteria and Text Color

Question

I am using a Macro to Sum up Values of a specific Debtor. It currently works fine but I need to add another Criteria;

Range("DebtorList_Payed")=Application.SumIf(Range("InvoiceList_Table_ItmCode"), _
  Range("Debtor_list_Debtors"), Range("InvoiceList_Price"))

I have another Range on the Invoice List Worksheet called Range("InvoiceList_Payed") in this row there is "CASH", "CREDIT", "(Custom) CASH" & "(Custom) CREDIT".

The text in the "CASH" & "(Custom) CASH" Rows is Green and the text in the "CREDIT" & "(Custom) CREDIT" Rows is Red

Using the existing Macro I need for it to only sum the Rows which in the corresponding Range("InvoiceList_Payed") the Text Color is Green,

So something like;

Range("DebtorList_Payed")=Application.SumIf(Range("InvoiceList_Table_ItmCode"), _
  Range("Debtor_list_Debtors"), Range("InvoiceList_Price") = _  
  Range("InvoiceList_Payed")> "Green"))

Answer 1

If you're using Excel 2007 or later you can use the SUMIFS function, and if you're in an earlier version you can nest the SUM and IF functions. The real problem is that there is no way to get the color of the cell from a worksheet function. You can use the CELL("color", REF) function IF AND ONLY IF the formatting on the cells is set so that the Green is positive and the Red is negative (or vice versa).

Of course, this would be easy to do using standard VBA functions/properties like RGB.

You could also just trigger off of the word "CASH" since both Green rows have that in common.

Example:

SUMIFS(Range("InvoiceList_Payed"), Range("InvoiceList_Table_ItmCode"), _  
  Range("Debtor_list_Debtors"), Range("InvoiceList_Payed"), "*CASH*")