sumifs

**rotem** · 03-23-2012, 12:04 PM

hello everybody
i tried to do some calcultion in excel but its seems that we disagree on some things coul you help me out
I want to compare between two strings (chars) and if the are equal i want to sum a thired column
the problem is that excel calculate only the first cell and not the all vector an even if the crctiria is true i get zero
i tried
= if(exect($B49:$B3 = $O$2),,$J$2 + $F$3:$F$49,$J$2)
=IF($B49:$B3 = $O$2,$J$2 + $F$3:$F$49,$J$2) + IF(B3:B49 = $O$3,$J$2 + $F$3:$F$49,$J$2) + IF(B3:B49 = $O$4,$J$2 + $F$3:$F$49,$J$2)
=SUMIFS($F$3:$F$49, $B$3:$B$49, "= $O$2")+SUMIFS($F$3:$F$49, $B$3:$B$49, "= $O$3")+SUMIFS($F$3:$F$49, $B$3:$B$49, "= $O$4")
=SUMIF(J2:J9,K2=J2,L2:L9)
nothing seems to work

**carsto** · 03-23-2012, 01:36 PM

from your formulas I can't figure out what you're trying to do, can you attach a sample workbook?
your formula is using columns B, O, J & F yet your description contains only (3) criteria

are you using the formula wizard? in XL2007 it is clear how to do this.

if you are not really using 2007, then you must use sumproduct

**rotem** · 03-23-2012, 05:15 PM

I am using 2007 and I just using function on the sheet, i am not using any wizard
although is will be nice to start using real programming in order to handle that excel

let say those are my col and now I want to sum the numbers that connected only to lion and cow
so in my sheet B is the first col F is the second one, O is the animale to compare and J is the ans (sum of numbers)
e.g
lion 50
goat 60
cow 70
horse 50
cow 50
lion 11
fish 12
cow 44
fish 666
dog 324
cow 245

**daddylonglegs** · 03-23-2012, 05:19 PM

You don't need the cell references in quotes so this should work

=SUMIFS($F$3:$F$49,$B$3:$B$49,$O$2)+SUMIFS($F$3:$F$49,$B$3:$B$49,$O$3)+SUMIFS($F$3:$F$49,$B$3:$B$49,$O$4)

or you can simplify by using this version

=SUMPRODUCT(SUMIFS($F$3:$F$49,$B$3:$B$49,$O$2:$O$4))

**DGagnon** · 03-23-2012, 05:20 PM

delete please

**Paul** · 03-23-2012, 05:21 PM

Hi Rotem, welcome to the forum.

Perhaps try:

=SUMIF(B1:B10,O1,F1:F10)+SUMIF(B1:B10,O2,F1:F10)

Assumes B1:B10 contains animals, F1:F10 contains counts of animals, O1 contains first animal you're searching for, O2 contains second animal you're searching for.

This will sum all values in F1:F10 where B1:B10 match either O1 or O2.

**rotem** · 04-03-2012, 12:13 PM

thanks for the replays
it works
I have a very stupid question I think =>
I want to create a constant jump in the formula range
let say in the formula I have a range of A1:A10
so in the next row the formula range will be A5:A10 and not A2:A11
thanks

**DGagnon** · 04-03-2012, 12:19 PM

you can use indirect to accomplish this, im not sure which formula you sided with, but something like this

=SUM(INDIRECT("A"&ROW()*4-3 &":A10"))

which will return the following refferences by row

Row 1 - A1:A10
Row 2 - A5:A10
Row 3 - A9:A10

and increasing by 4 at a time, you can do the same with the end of the range too like this

=SUM(INDIRECT("A"&ROW()*4-3 &":A"&ROW()*4+6))

**rotem** · 04-04-2012, 03:24 PM

thanks using some math and it worked
I tried use that function in order to create interpolation using the trend function
but it didn't seems to be giving me the desired results
I used 15 samples of i and j from one set data and a known i from the other data set but it didn't gave me a closet result to out I
expected, of course it's have a lot to do with the fact that the values are not monotonically, evenly spaced and are not linearly in
their behavior
how can I predict the next value... my purpose is to evaluate the error between the two data sets