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

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

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

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

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

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

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

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

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

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

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

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

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