If value is true add cell to the right...

**minnewastawsd** · 10-05-2011, 12:50 PM

Any time a cell A$ (or A2 till A45 ect...) = John Smith, add cell to the right

This will have to be done for MANY cells and it won’t make sense to type out long stings of code like the one below. The code below does do what I want, but there must be a simpler way

Code is E2
=IF(A1=D2,B1,0)+IF(A2=D2,B2,0)+IF(A3=D2,B3,0)+IF(A4=D2,B4,0)+IF(A5=D2,B5,0)+IF(A6=D2,B6,0)

A---------------------B-----C--------D-------------------E
1 John Smith-----4--------------Totals
2 John Smith-----2--------------John Smith------14
3 Jane Doe--------5--------------Jane Doe---------16
4 John Smith-----8
5 Jane Doe--------6
6 Jane Doe--------5

**NBVC** · 10-05-2011, 12:54 PM

Do you mean?

=SUMIF($A$1:$X$10,"John Smith",$B$1:$Y$10)

where your data is in A1:Y10... note the offset in ranges in the formula.

you can replace "John Smith" with a cell reference containing the name too, so you can copy formula down.

**ConneXionLost** · 10-05-2011, 12:56 PM

How about:

=SUMIF($A$2:$A$45,$D$2,$B$2:$B$45)

Cheers,

**NBVC** · 10-05-2011, 01:22 PM

I guess I was assuming that there were columns of names with columns of numbers ...

If you only have one column of names and numbers, then go with CXL's formula.

**minnewastawsd** · 10-06-2011, 10:55 AM

Thanks for the replies! With the first code, when I first pasted it in it worked. Then tried replacing the name with Jane Doe and it went to zero. So tried putting it back to John Smith and it stayed at zero. quotes were in place and spelling was identical. Haven't encountered that before, re copied and pasted and nothing happened...:S

Second code worked fine and had no trouble manipulating it.

Both give me good ideas for future stuff, thanks!

**NBVC** · 10-06-2011, 10:58 AM

Where did you put the first code.. was it within range A1:Y10?

If not, it should work too, but don't think that is what you want anyways.

**minnewastawsd** · 10-06-2011, 11:19 AM

I have 3 coloums that need the same treatment for each name, is it most efficient to do this?

=SUMIF($B$237:$B$458,$I$474,$D$237:$D$458)+SUMIF($I$237:$I$458,$I$474,$K$237:$K$280)+SUMIF($P$237:$P $458,$I$474,$R$237:$R$458)

-----A-----B-----C-----D-----E-----F-----G-----H-----I-----J-----K-----L-----M-----N-----O-----P-----Q-----R-----S-----T
237-------name-----number---------------------------name-----number-------------------------name-----number
238-------name-----number---------------------------name-----number-------------------------John------1
239-------John---------3---------------------------------name-----number-------------------------name-----number
240-------Jane---------4---------------------------------name-----number-------------------------name-----number
241-------name-----number---------------------------John-------8---------------------------------name-----number
242-------name-----number---------------------------name-----number-------------------------Jane------6
...
458
The same name could appear in any and all of the name boxes
-------Total
Jane 10
John 12

**NBVC** · 10-06-2011, 11:22 AM

Probably that is the best way... but this, my original version, might also work.. dependending on what is in the other columns between.

=SUMIF($B$237:$P$458,$I$474,$D$237:$R$458)

**minnewastawsd** · 10-06-2011, 12:12 PM

Originally Posted by NBVC

Probably that is the best way... but this, my original version, might also work.. dependending on what is in the other columns between.

=SUMIF($B$237:$P$458,$I$474,$D$237:$R$458)

your right, it worked like a charm! Thanks! now I just have to figure out HOW it works.

I can see that if anything between $B$237:$P$458 = $I$474 then... How does it know what to add?

**NBVC** · 10-06-2011, 01:15 PM

Basically it summing the values that are offset from the criteria column the same distance from where it finds a match to the criteria...

Both ranges have to be the same size (height and width) and the first range starts at first column to search criteria, and ends at last column for criteria search, the sum column then starts at the first summing column for the first search column and ends proportionately at the last summing column for the last search column.