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