Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple formulas invovling ranges
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multiple formulas invovling ranges
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 "Devon" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
complex count formulas using multiple criteria in different ranges | Excel Discussion (Misc queries) | |||
Crteating Multiple GIFS from Multiple Ranges -- need someone to test my code to see why it fails | Excel Programming | |||
Crteating Multiple GIFS from Multiple Ranges -- need someone to test my code to see why it fails | Excel Programming | |||
a problem with if invovling a range of cells | Excel Worksheet Functions | |||
Creating Formulas That Accept Multiple Ranges | Excel Programming |