SUMIF & OFFSET

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have nested an OFFSET formula in a SUMIF formula to give the sumrange element for the SUMIF formula. If the OFFSET formula gives a range of more than one column the SUMIF still only sums the first column of the OFFSET answer not the range. Is this the default for OFFSET or is there an alternative
I have included the formula

=SUMIF(A2:E6,D11,(OFFSET(A2:A6,,1,,2))

Go raibh maith agat
 
depending on how many ranges you wish to sum, and is this going to be
static range, or dynamic?

if you are only wanting to sum the first two column (B & C) rows 1 t
6, can't you just use excels native functionality?...

=SUMIF(A2:E6,D11,B2:C6)

hope this helps
 
Hi Trevor,
I think the problem you are running into is with the SUMIF. It only adds the value from the first column in the sum_range. You will need to use the following formula if you want to add values from two columns:

=SUMIF(A2:E6,D11,OFFSET(A2:A6,,1,,1))+SUMIF(A2:E6,D11,OFFSET(A2:A6,,2,,1))

Good Luck,
Mark Graesser
(e-mail address removed)
Boston MA

----- Trevor wrote: -----

I have nested an OFFSET formula in a SUMIF formula to give the sumrange element for the SUMIF formula. If the OFFSET formula gives a range of more than one column the SUMIF still only sums the first column of the OFFSET answer not the range. Is this the default for OFFSET or is there an alternative?
I have included the formula:

=SUMIF(A2:E6,D11,(OFFSET(A2:A6,,1,,2)))

Go raibh maith agat
 
Here's another formula which does what you need (I think)

=SUMPRODUCT((A2:E6=D11)*((B2:F6)+(C2:G6))

For any cell that has the value of D11, this will add the two values immediately to it's right

Good Luck
Mark Graesse
(e-mail address removed)
Boston M

----- Trevor wrote: ----

I have nested an OFFSET formula in a SUMIF formula to give the sumrange element for the SUMIF formula. If the OFFSET formula gives a range of more than one column the SUMIF still only sums the first column of the OFFSET answer not the range. Is this the default for OFFSET or is there an alternative
I have included the formula

=SUMIF(A2:E6,D11,(OFFSET(A2:A6,,1,,2))

Go raibh maith agat
 
Thanks Guys,

All the solutions have worked but I think my real problem
is with the dynamic nature of the ranges I am trying to
include. I really need the sumrange to be the number of
columns produced by the offset formula. I would appreciate
anything else if you have it and thanks again

Trevor

-Original Message-----
Hi Trevor,
I think the problem you are running into is with the
Click to expand...
SUMIF. It only adds the value from the first column in
the sum_range. You will need to use the following formula
if you want to add values from two columns:
=SUMIF(A2:E6,D11,OFFSET(A2:A6,,1,,1))+SUMIF (A2:E6,D11,OFFSET(A2:A6,,2,,1))

Good Luck,
Mark Graesser
(e-mail address removed)
Boston MA

----- Trevor wrote: -----

I have nested an OFFSET formula in a SUMIF formula
Click to expand...
to give the sumrange element for the SUMIF formula. If the
OFFSET formula gives a range of more than one column the
SUMIF still only sums the first column of the OFFSET
answer not the range. Is this the default for OFFSET or
is there an alternative?
 
Hi Trevor
Your OFFSET function always produces 2 columns. Both of the solutions I provided will include the numbers from both columns in your sum. Am I missing sonething

Regards
Mark Graesse
(e-mail address removed)
Boston M

----- Trevor wrote: ----

Thanks Guys

All the solutions have worked but I think my real problem
is with the dynamic nature of the ranges I am trying to
include. I really need the sumrange to be the number of
columns produced by the offset formula. I would appreciate
anything else if you have it and thanks agai

Trevo

-Original Message----
Hi Trevor
I think the problem you are running into is with the
Click to expand...
SUMIF. It only adds the value from the first column in
the sum_range. You will need to use the following formula
if you want to add values from two columns
Mark Graesse
(e-mail address removed)
Boston M
Click to expand...
to give the sumrange element for the SUMIF formula. If the
OFFSET formula gives a range of more than one column the
SUMIF still only sums the first column of the OFFSET
answer not the range. Is this the default for OFFSET or
is there an alternative
 
Back
Top