Multiple formulas invovling ranges

[Guest]

I am trying to write a formula and struggling to get it to work. The goal is
to grab a specific value (ex. “2â€) from two specific columns on a separate
tab. If a row has that value in either cell then I want to read a formula
from a different cell on that row. This would be repeated for each row that
has a “2: in that specific column. I then want the various formulas
totaled.

So, for example I create a formula in cell C7 on tab Test 1, that looks at
Column AD and AZ on tab Test 2. If cell AD7=2 and/or AZ=2, then we look at
cell BK7 which is using the following formula
=IF(BH7=0,0,(COUNTIF(BG7,"P"))). I want to do this as a range so that if
AD10, AD12, AZ20, AZ25, etc have a value of 2 then we look at BK10, BK12,
BK20, BK25, etc. I then want to sum all of the BK cells that were just read.

I have tried various combination of Countif statements and ranges and cannot
get a good value.

 

[Guest]

Try this special function

call with
=SpecialCountIf(E1:E5,F1:F5,G1:G5,2)



Function SpecialCountIf(Compare1 As Range, Compare2 As Range, _
CountIf As Range, CompareWith)

MyCompare1 = Compare1
MyCompare2 = Compare2
MyCountIf = CountIf

For NextCount = 1 To Compare1.Count
If (MyCompare1(NextCount, 1) = CompareWith) Or _
(MyCompare2(NextCount, 1) = CompareWith) Then

SpecialCountIf = SpecialCountIf + _
MyCountIf(NextCount, 1)
End If

Next NextCount

End Function