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

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

[Toppers]

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

[Biff]

Hi!

Try this:

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

Biff

"hodgsonk" <[email protected]> wrote in message
news:[email protected]...
>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)?
>
>

[Biff]

Or, simply:

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

Biff

"Biff" <[email protected]> wrote in message
news:[email protected]...
> Hi!
>
> Try this:
>
> =SUMPRODUCT(--(MOD(COLUMN(B1:H1)-COLUMN(B1),2)=0),--(A1:G1="F"),B1:H1)
>
> Biff
>
> "hodgsonk" <[email protected]> wrote in message
> news:[email protected]...
>>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)?
>>
>>
>
>

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


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

[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

"Biff" <[email protected]> wrote in message
news:[email protected]...
> Or, simply:
>
> =SUMPRODUCT(--(A1:G1="F"),B1:H1)
>
> Biff
>
> "Biff" <[email protected]> wrote in message
> news:[email protected]...
>> Hi!
>>
>> Try this:
>>
>> =SUMPRODUCT(--(MOD(COLUMN(B1:H1)-COLUMN(B1),2)=0),--(A1:G1="F"),B1:H1)
>>
>> Biff
>>
>> "hodgsonk" <[email protected]> wrote in message
>> news:[email protected]...
>>>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)?
>>>
>>>
>>
>>
>
>

[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" 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" <[email protected]> wrote in message
> news:[email protected]...
> > Or, simply:
> >
> > =SUMPRODUCT(--(A1:G1="F"),B1:H1)
> >
> > Biff
> >
> > "Biff" <[email protected]> wrote in message
> > news:[email protected]...
> >> Hi!
> >>
> >> Try this:
> >>
> >> =SUMPRODUCT(--(MOD(COLUMN(B1:H1)-COLUMN(B1),2)=0),--(A1:G1="F"),B1:H1)
> >>
> >> Biff
> >>
> >> "hodgsonk" <[email protected]> wrote in message
> >> news:[email protected]...
> >>>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)?
> >>>
> >>>
> >>
> >>
> >
> >
>
>
>

[Biff]

Try this:

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

Biff

"hodgsonk" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > Or, simply:
>> >
>> > =SUMPRODUCT(--(A1:G1="F"),B1:H1)
>> >
>> > Biff
>> >
>> > "Biff" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> Hi!
>> >>
>> >> Try this:
>> >>
>> >> =SUMPRODUCT(--(MOD(COLUMN(B1:H1)-COLUMN(B1),2)=0),--(A1:G1="F"),B1:H1)
>> >>
>> >> Biff
>> >>
>> >> "hodgsonk" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >>>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)?
>> >>>
>> >>>
>> >>
>> >>
>> >
>> >
>>
>>
>>

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

> Try this:
>
> =SUMPRODUCT(--(A2:G2=A1),--(A3:G3="F"),B3:H3)
>
> Biff
>
> "hodgsonk" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > Or, simply:
> >> >
> >> > =SUMPRODUCT(--(A1:G1="F"),B1:H1)
> >> >
> >> > Biff
> >> >
> >> > "Biff" <[email protected]> wrote in message
> >> > news:[email protected]...
> >> >> Hi!
> >> >>
> >> >> Try this:
> >> >>
> >> >> =SUMPRODUCT(--(MOD(COLUMN(B1:H1)-COLUMN(B1),2)=0),--(A1:G1="F"),B1:H1)
> >> >>
> >> >> Biff
> >> >>
> >> >> "hodgsonk" <[email protected]> wrote in message
> >> >> news:[email protected]...
> >> >>>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)?
> >> >>>
> >> >>>
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >>
>
>
>

[Biff]

See these for more info:

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

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

Biff

"hodgsonk" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > Or, simply:
>> >> >
>> >> > =SUMPRODUCT(--(A1:G1="F"),B1:H1)
>> >> >
>> >> > Biff
>> >> >
>> >> > "Biff" <[email protected]> wrote in message
>> >> > news:[email protected]...
>> >> >> Hi!
>> >> >>
>> >> >> Try this:
>> >> >>
>> >> >> =SUMPRODUCT(--(MOD(COLUMN(B1:H1)-COLUMN(B1),2)=0),--(A1:G1="F"),B1:H1)
>> >> >>
>> >> >> Biff
>> >> >>
>> >> >> "hodgsonk" <[email protected]> wrote in message
>> >> >> news:[email protected]...
>> >> >>>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)?
>> >> >>>
>> >> >>>
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>
>>
>>

[hodgsonk]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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" <[email protected]> wrote in message
> >> >> news:[email protected]...
> >> >> > Or, simply:
> >> >> >
> >> >> > =SUMPRODUCT(--(A1:G1="F"),B1:H1)
> >> >> >
> >> >> > Biff
> >> >> >
> >> >> > "Biff" <[email protected]> wrote in message
> >> >> > news:[email protected]...
> >> >> >> Hi!
> >> >> >>
> >> >> >> Try this:
> >> >> >>
> >> >> >> =SUMPRODUCT(--(MOD(COLUMN(B1:H1)-COLUMN(B1),2)=0),--(A1:G1="F"),B1:H1)
> >> >> >>
> >> >> >> Biff
> >> >> >>
> >> >> >> "hodgsonk" <[email protected]> wrote in message
> >> >> >> news:[email protected]...
> >> >> >>>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)?
> >> >> >>>
> >> >> >>>
> >> >> >>
> >> >> >>
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

[Biff]

You're welcome. Thanks for the feedback!

Biff

"hodgsonk" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > 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" <[email protected]> wrote in message
>> >> >> news:[email protected]...
>> >> >> > Or, simply:
>> >> >> >
>> >> >> > =SUMPRODUCT(--(A1:G1="F"),B1:H1)
>> >> >> >
>> >> >> > Biff
>> >> >> >
>> >> >> > "Biff" <[email protected]> wrote in message
>> >> >> > news:[email protected]...
>> >> >> >> Hi!
>> >> >> >>
>> >> >> >> Try this:
>> >> >> >>
>> >> >> >> =SUMPRODUCT(--(MOD(COLUMN(B1:H1)-COLUMN(B1),2)=0),--(A1:G1="F"),B1:H1)
>> >> >> >>
>> >> >> >> Biff
>> >> >> >>
>> >> >> >> "hodgsonk" <[email protected]> wrote in message
>> >> >> >> news:[email protected]...
>> >> >> >>>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)?
>> >> >> >>>
>> >> >> >>>
>> >> >> >>
>> >> >> >>
>> >> >> >
>> >> >> >
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>