Help with "If" Formula/statement

[maza_minx]

Hi All,

Hope you can help me out with this one. I have a sheet which contains rows of data. Each column has a name and the column right next to it contains a number. This pattern continues for about 8 columns.

E.g.
Name, #, Name, #, Name, #, Name, #

I need to build a formula that will say If Name = " ", then count # in the next column.

E.g.
Alex, 8 - So the formula will know that if the "name" column contains the text "Alex" it will then count the number 8 which is posted in the next column.

Please keep in mind that I will need this formula to eventually sum up all numbers where Alex (or different names) appears. Any help or guidance you can provide would be greatly appreciated.

Thanks.

 

[just_jon]

Welcome to MrExcel -- try

Book2
	A	B	C	D	E	F	G	H
1	Name	Number	Name	Number	Name	Number	Name	Number
2	alex	34	foo	56	alex	1	foo	100
3	foo	3	alex	13	foo	2	foo	80
4	alex	9	alex	45	foo	3	alex	60
5	foo	7	foo	99	alex	4	foo	40
6
7	alex	166
Sheet2

 

[Gates Is Antichrist]

A less concise approach (but which provides more detail ) uses sumif:

Underneath column A, type (or preferably paste) Alex and the other names. Let's say that the first is in A20. Then
B20=SUMIF(A20,A$1:A$19)
and copy down, so that e.g.
B21=SUMIF(A21,A$1:A$19)

You can do the same process for columns C/D, etc. and combine as desired. Clear?

 

[maza_minx]

Hi Jon,

Thanks for that detailed reply. I tried to copy the formula directly into my sheet and change the coordinates accordingly but it does not seem to be picking up the numbers. Value is coming out as ZERO...any suggestions?

 

[just_jon]

Which formula?

 

[Gates Is Antichrist]

Highlight the formula's cell (left-click it), then hit F2 to see if it's operating on the desired target range. The range to be "sumproducted" should be outlined in blue then. (Hit escape key when done)

I suspect that you need $ anchors, for everything except the A7 in jon's fine example. The above technique will expose this.

 

[maza_minx]

GOT IT!!!

Thanks alot for your great help guys.