Select a range of non-adjacent cells in Excel?
 

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)?

Select a range of non-adjacent cells in Excel?
 

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

"hodgsonk" wrote:

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)?

Select a range of non-adjacent cells in Excel?
 

.... typo ....


=Sum_FS(A1:F1,"F")

"Toppers" wrote:



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

"hodgsonk" wrote:

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)?

Select a range of non-adjacent cells in Excel?
 

Hi!

Try this:

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

Biff

"hodgsonk" wrote in message
...
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)?

Select a range of non-adjacent cells in Excel?
 

Or, simply:

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

Biff

"Biff" wrote in message
...
Hi!

Try this:

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

Biff

"hodgsonk" wrote in message
...
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)?

Select a range of non-adjacent cells in Excel?
 

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"?


"Toppers" wrote:

... typo ....


=Sum_FS(A1:F1,"F")

"Toppers" wrote:



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

"hodgsonk" wrote:

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)?

Select a range of non-adjacent cells in Excel?
 

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

"Biff" wrote in message
...
Or, simply:

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

Biff

"Biff" wrote in message
...
Hi!

Try this:

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

Biff

"hodgsonk" wrote in message
...
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)?

Select a range of non-adjacent cells in Excel?
 

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" wrote:

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

"Biff" wrote in message
...
Or, simply:

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

Biff

"Biff" wrote in message
...
Hi!

Try this:

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

Biff

"hodgsonk" wrote in message
...
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)?

Select a range of non-adjacent cells in Excel?
 

Try this:

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

Biff

"hodgsonk" wrote in message
...
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" wrote:

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

"Biff" wrote in message
...
Or, simply:

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

Biff

"Biff" wrote in message
...
Hi!

Try this:

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

Biff

"hodgsonk" wrote in message
...
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)?

Select a range of non-adjacent cells in Excel?
 

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" wrote:

Try this:

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

Biff

"hodgsonk" wrote in message
...
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" wrote:

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

"Biff" wrote in message
...
Or, simply:

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

Biff

"Biff" wrote in message
...
Hi!

Try this:

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

Biff

"hodgsonk" wrote in message
...
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)?

Select a range of non-adjacent cells in Excel?
 

See these for more info:

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

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

Biff

"hodgsonk" wrote in message
...
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" wrote:

Try this:

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

Biff

"hodgsonk" wrote in message
...
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" wrote:

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

"Biff" wrote in message
...
Or, simply:

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

Biff

"Biff" wrote in message
...
Hi!

Try this:

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

Biff

"hodgsonk" wrote in message
...
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)?

Select a range of non-adjacent cells in Excel?
 

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

"Biff" wrote:

See these for more info:

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

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

Biff

"hodgsonk" wrote in message
...
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" wrote:

Try this:

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

Biff

"hodgsonk" wrote in message
...
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" wrote:

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

"Biff" wrote in message
...
Or, simply:

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

Biff

"Biff" wrote in message
...
Hi!

Try this:

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

Biff

"hodgsonk" wrote in message
...
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)?

Select a range of non-adjacent cells in Excel?
 

You're welcome. Thanks for the feedback!

Biff

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

"Biff" wrote:

See these for more info:

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

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

Biff

"hodgsonk" wrote in message
...
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" wrote:

Try this:

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

Biff

"hodgsonk" wrote in message
...
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" wrote:

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

"Biff" wrote in message
...
Or, simply:

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

Biff

"Biff" wrote in message
...
Hi!

Try this:

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

Biff

"hodgsonk" wrote in message
...
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)?