Countifs_two text criteria and one color criteria

**alinda** · 09-22-2011, 04:56 AM

Dear all,

It would be great if you could help me with the following.

Key points:
1) Excel: I use Excel 2007, and have basic understanding of VBA
2) Issue: I need Excel to count the number of cells that meet Criteria A (text), Criteria B (text) and Criteria C (red color).

Question:
I now however need to count the cells that meet Criteria A (text), Criteria B(text) AND are Red.

I tried to do this with:
=Countifs (range “column A”, Criteria A “text”, range “column B”, Criteria B “text”, range “column C”, Criteria C color red)For Criteria C I however do not know which formula to use to get the criteria “red cell”. I tried all, but nothing worked.
I run the color module by Chip Pearson, but this gave an error message.

Hope you’ll be able to provide me with the answer…

Many thanks!

Best regards,

Alinda

**zbor** · 09-22-2011, 05:03 AM

Your profile say you using 2003... Please change it... Solution might depend of XL version you use.

In XL 2007 you can add filter in first row. Then filter out all criteria(s) you need (including color) and SUBTOTAL your result.

Or do you want macro?

**alinda** · 09-22-2011, 05:38 AM

Hi!

Thanks for your quick reply. I will change my version of Excel to 2007.
Yes, I would like to use a formula or macro, as the 'countif text AND text AND red color' need to calculate on the background in a separate table.

Would you be able to help me on another solution?

Many thanks!

Best, Alinda

**Marcol** · 09-22-2011, 05:58 AM

A sample of your workbook would go a long way towards helping you.
It should clearly illustrate your problem and not contain any sensitive data.

From Forum FAQs
How do I attach a file to a post?

To attach a file to your post, you need to be using the main 'New Post' or 'New Thread' page and not 'Quick Reply'. To use the main 'New Post' page, click the 'Post Reply' button in the relevant thread.

On this page, below the message box, you will find a button labelled 'Manage Attachments'. Clicking this button will open a new window for uploading attachments. You can upload an attachment either from your computer or from another URL by using the appropriate box on this page. Alternatively you can click the Attachment Icon to open this page.

To upload a file from your computer, click the 'Browse' button and locate the file. To upload a file from another URL, enter the full URL for the file in the second box on this page. Once you have completed one of the boxes, click 'Upload'.

Once the upload is completed the file name will appear below the input boxes in this window. You can then close the window to return to the new post screen.

**Domski** · 09-22-2011, 06:03 AM

You can use a udf like this which will return TRUE if a cell is coloured red. This can then be tested in your formula.

Public Function ColourRed(rngColcell As Range) As Boolean

If rngColcell.Interior.ColorIndex = 3 Then ColourRed = True

End Function

It's not a volatile function though so the results of the formula won't change if you change the colour of a cell unless you perform a sheet recalc.

Dom

**alinda** · 09-22-2011, 06:34 AM

Thanks for the help!

I ran the function in VBA, and see that the list ColourRed is added. However, I do not know how to add in the third condition and third range of the Countifs formula.

Please find a simplified worksheet attached. In the analysis worksheet, I want to add a column counting the number of red months cells, for the criteria BG and Window.

Hope this helps...

Many thanks!

**Domski** · 09-22-2011, 06:47 AM

In your example you would use =ColourRed(C2) in D2 copied down and then your formula would be:

=COUNTIFS(Data!$A$2:$A$25,Analysis!A6,Data!$B$2:$B$25,Analysis!$B$3,Data!$D$2:$D$25,TRUE)

Dom

**alinda** · 09-22-2011, 09:40 PM

Thanks Domski!
I however, would not like to add a new column in the Data sheet, as I have more than 30 columns with red colors in the original worksheet.
Simply adding

=COUNTIFS(Data!$A$2:$A$25,Analysis!B6,Data!$B$2:$B$25,Analysis!$B$3,Data!C2:C25,(ColourRed(Data!$C$2:$C$25)="TRUE"))

does not work. Is there another solution?

Cheers and many many thanks!

Alinda

**Marcol** · 09-23-2011, 06:43 AM

Try this

!/. Create a dynamic named range in Sheet "Data"
Name:= "LaunchWindow"
Refers To:=

=OFFSET(Data!$B2,0,0,COUNTA(Data!$B:$B)-1,1)

2/. Put this Function in a standard module

Option Explicit

Public Function CountRed(strBG As String, strWindow As String) As Long
    Dim Cell As Range
    
        For Each Cell In Sheets("Data").Range("LaunchWindow")
            If Cell.Offset(0, 1).Interior.ColorIndex = 3 Then
                If Cell.Offset(0, -1) = strBG And Cell = strWindow Then
                    CountRed = CountRed + 1
                End If
            End If
        Next
    CountRed = CountRed
End Function

3/. In Sheet "Analysis" C6

=CountRed($A6,$B$3)

Drag/Fill Down.

Changing the fill colour in a cell will not trigger events or calculation.

If you change the colour in the named range "LaunchWindow", you'll have to either, manually calculate, or wait until there is some other change in the workbook.

A way to partially cure this is to add this line to to function after the Dim statements

Application.Volatile

Then in the Workbook Module

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Application.Calculate
End Sub

**snb** · 09-23-2011, 07:34 AM

Function isred(sn, c02, c03)
 For Each it In sn
  If it.Interior.ColorIndex = 3 And it.Offset(, -2) & it.Offset(, -1) = c02 & c03 Then isred = isred + 1
 Next
End Function

In cell A2:

PHP Code:


=isred(C4:C20,A6,B3)

**alinda** · 09-26-2011, 02:10 AM

Dear Marcol and dear Snb,

Both of your solutions work perfectly fine in my trial Excel file! Thanks for your valuable insights.

The problem however is, that they don't work in the Excel file that they need to work in.
Let me show you the file.

In the sheet 'MIP analysis', I want to have a table which calculates the number of red cells per MIP Dashboard column (see headers in MIP analysis) for the BG [column A in MIP Dashboard] and the right Launch Window [column L in MIP Dashboard] which is selected in cell B2.

It would be easiest if this could be done from a countifs formula, but I understand from your replies this is not possible.

Is there another option? It should be possible right?

10000 times thank you for all your thoughts

Many thanks and best regards,

Alinda