Conditional Formula to work in conjunction with indirect reference

[OleMiss2010]

I have a formula that gives me a count of all instances of a certain things across an entire workbook. The formula is repeated several times for different issues, but an essentially the formula is:

=SumProduct(CountIf(Indirect("'"&SheetList&"'!C:C")"="&C11)

SheetList is a defined name that lists out all of the sheets in the workbook. And C11 is a cell that contains the issue that the formula is counting.


This all works perfectly. However, I have a new caveat that I need to add for which I would prefer to use a formula rather than a vba.


I need to make the formula only count instances that meet the above criteria AND where the cell below whereever it finds the issue throughout the workbook does NOT say "Approved" or "Cured". Essentially throughout the workbook there are a varying number of instances where somewhere in column C there may be certain issues noted and in the cell immediately below that it is noted if this issue was "Approved" or "Cured". I need to only count the issues that are "Not Approved" or have not been commented on. I could obviously do a hidden field out to the side of the sheets mentioned in the SheetList, but there are a lot of sheets and a lot of potential issues.


Any thoughts about how I can make this formula work? The trick is making the formula look in the row below the instance of the issue.

 

[Luke M]

Here's a sub that might work, or get your started.

[pre]

'Use this if you don't need the text comparisons to be
'case sensitive
Option Compare Text
Sub ConditionalFind()
Dim xCriteria As String
Dim FindMe As String
Dim firstAddress As String
Dim c As Range
'formula recalculates when changes are made to workbook
Application.Volatile
xCriteria = "Not Approved"

'What are you looking for?
FindMe = "Bob"

i = 0
For Each ws In ThisWorkbook.Worksheets
With ws.Cells
Set c = .Find(What:=FindMe)
If Not c Is Nothing Then
firstAddress = c.Address
Do
If c.Offset(1, 0) = xCriteria Then
i = i + 1
End If
Set c = .FindNext(after:=c)
Loop While Not c Is Nothing And firstAddress <> c.Address
End If
End With
Next ws

'Spit out value
ActiveCell.Value = i
End Sub

[/pre]

 

[Luke M]

On second thought, I may have misread...I thought you already had a formula that worked. Or are you wanting a worksheet formula?

 

[OleMiss2010]

My formula works only at counting the instances of whatever the issue in C11 is. It does not rule out the instances where that issue has been "Approved" or "Cured".


I would like to avoid a vba on this one it that is reasonably possible.

 

[OleMiss2010]

So I came up with a workable solution. I created a hidden column on all of the SheetList sheets that listed any issue where the cell below said "Cured" or "Approved" then to my original formula I subtracted the instances of the issue in the new hidden column. This seems to work.