Select a range of non-adjacent cells in Excel?

[hodgsonk]

I have a series of data values in non-adjacent columns in an excel spreadsheet.
In the following example, assume the | (vertical bar) refers to the start of
a new cell:

F|45|A|30|F|15|F|10

Using the SUMIF formula, I want to add all numbers which are preceded by a
cell containing the letter F.

SUMIF(A1:G1,"F",B1:H1)

What I need to do is specify a range of every second cell in the row
(starting with Cell A1) for validating they equal F, and a range of the
alternating cells (starting in column B1) for the range containing the data
to add. How can I specify these ranges (I can't name each cell individually
as I have more than 30 cells to add up in my real life situation and the IF
function allows selection of no more than 30 values)?

 

[Toppers]

Try this UDF:

=sum_FS("A1:F1","F")

HTH

Function Sum_FS(ByRef rng As Range, ByVal Comparator As String) As Double
ncol = rng.Count
sumf = 0
For col = 2 To ncol
If rng(1, col - 1) = Comparator Then
sumf = sumf + rng(1, col)
End If
Next col
Sum_Fs = sumf
End Function

 

[Toppers]

.... typo ....


=Sum_FS(A1:F1,"F")

Try this UDF:

=sum_FS("A1:F1","F")

HTH

Function Sum_FS(ByRef rng As Range, ByVal Comparator As String) As Double
ncol = rng.Count
sumf = 0
For col = 2 To ncol
If rng(1, col - 1) = Comparator Then
sumf = sumf + rng(1, col)
End If
Next col
Sum_Fs = sumf
End Function

 

[Biff]

Hi!

Try this:

=SUMPRODUCT(--(MOD(COLUMN(B1:H1)-COLUMN(B1),2)=0),--(A1:G1="F"),B1:H1)

Biff

 

[Biff]

Or, simply:

=SUMPRODUCT(--(A1:G1="F"),B1:H1)

Biff

 

[hodgsonk]

Thanks, Toppers. This works just as I asked for. I'm not sure I quite
understand all of the UDF. Which value would I change if I only wanted it to
compare every fourth cell for an "F"?

 

[Biff]

Hmmm.........

Seems to me that your use of Sumif will do the same thing.

Am I missing something? I'm having one of those days!

Biff

 

[hodgsonk]

You are correct Biff! I created this example from a much more complex
spreadsheet, and I guess I did not do a complete enough job of explaining my
problem. I actually need to use two different criteria to evaluate against,
and both criteria must be met successfully before I add the adjacent cells.
Let me try another shot at this:

A1 = June
A2 = June C2 = June E2 = July G2 = July
A3 = F B3 = 45 C3 = A D3 = 30 E3= F F3 = 15 G3 = F H3 = 10

I need to evaluate each cell in row 2 against the value in cell A1. When
the values match, I need to evaluate that the corresponding cell in row 3 is
equal to F, and when it does, I need to add the adjacent value.

EG: If A2 = A1, then check to see if A3 = F and when it does, add B3. Then
I move on to check C2 = A1, , then check to see if C3 = F and when it does
not, I move on without adding D3. Then I move on to check E2 = A1, and when
it does not, I move on to G2, etc.

I hope this makes sense. Thanks for taking the time to help.

 

[Biff]

Try this:

=SUMPRODUCT(--(A2:G2=A1),--(A3:G3="F"),B3:H3)

Biff

 

[hodgsonk]

This is fantastic, Biff. I had tried the same formula previously, but did
not have the dashes in it. What is their significance, as this appears to be
what made it work now?

 

[Biff]

See these for more info:

http://xldynamic.com/source/xld.SUMPRODUCT.html

http://mcgimpsey.com/excel/formulae/doubleneg.html

Biff

 

[hodgsonk]

Awesome Biff. Thanks a lot for your diligence and these fantastic links.
I've learned a lot on this escapade!

 

[Biff]

You're welcome. Thanks for the feedback!

Biff

Awesome Biff. Thanks a lot for your diligence and these fantastic links.
I've learned a lot on this escapade!