Getting the sum...the complicated way

[Drahmee]

Hi,

I'm looking to SUM a set of values based on the name in the cell immediately to the left of the value, HOWEVER the cell to the left will be in a random column/row on the sheet, and there will be one name/value set for each week of the year. Thus, for week one, the name might be in cell A14, but in the next week, the name would be in cell C12, then E23, then G9, and so on. The corresponding value would be in the cell to the immediate right, as stated above.

Any help would be greatly appreciated!

Drahmee

 

[Rick Rothstein]

Hi,

I'm looking to SUM a set of values based on the name in the cell immediately to the left of the value, HOWEVER the cell to the left will be in a random column/row on the sheet, and there will be one name/value set for each week of the year. Thus, for week one, the name might be in cell A14, but in the next week, the name would be in cell C12, then E23, then G9, and so on. The corresponding value would be in the cell to the immediate right, as stated above.

Assuming your data is confined to Columns A:M (change as needed) and the name you want to find is "Drahmee", then...

=SUMIF(A:M,"Drahmee",B:N)

Note that the B:N range must be the range that is offset from the A:M range by one column.

 

[AkaTrouble]

if you create the formula without creating an absolute reference to the cell to the left (no $A$1 dollar signs) when you copy the formula it will update the cell to the left from the cell you put the formula in

 

[Drahmee]

Hi,

I'm using Office 2007, and I'm trying to sum 52 values based on the name in the cell immediately to the left of the value, HOWEVER each of the 52 name/value sets will be in a different row on the sheet. Thus, the first name might be in cell A14, but the next one might be in cell C12, then E23, then G9, and so on. The corresponding value would be in the cell to the immediate right, as stated above.

Any help would be greatly appreciated!

Drahmee

 

[Drahmee]

AkaTrouble,

Thank you for responding, but I'm not that versed in Excel, obviously. I'm a bit confused by your reply.

I'm looking to find the 52 times a specific name is in a spreadsheet, and SUM the 52 values to the immediate right of the names.

Thanks,
Drahmee

 

[Drahmee]

Rick Rothstein,

Thank you, I'll try that as soon as I get back to my computer!

Thanks so much to both of you for your help!!

Sincerely,
Drahmee

 

[AkaTrouble]

AkaTrouble,

Thank you for responding, but I'm not that versed in Excel, obviously. I'm a bit confused by your reply.

Drahmee

in plain language i was saying create the =SUM() for the first time and copy the formula to other locations that you need same SUM

so if the figures you need are in column A rows 1 to 12 the formula in B1 would equal

=SUM(A1:A12)

once you have this formula you can copy it to any cell and it will always SUM the column to the left going down 12 rows

 

[Drahmee]

Assuming your data is confined to Columns A:M (change as needed) and the name you want to find is "Drahmee", then...

=SUMIF(A:M,"Drahmee",B:N)

Note that the B:N range must be the range that is offset from the A:M range by one column.

So, what would I do if I wanted to find a person's name, on another sheet in the same file, and have it read the number two columns to the right?

And thank you in advance!

(IE. Looking for the number two columns to the right of "Drahmee" in a sheet named "April")

 

[Rick Rothstein]

So, what would I do if I wanted to find a person's name, on another sheet in the same file, and have it read the number two columns to the right?

And thank you in advance!

(IE. Looking for the number two columns to the right of "Drahmee" in a sheet named "April")

Try this...

=SUMIF(April!A:M,"Drahmee",April!C:O)

 

[Drahmee]

Try this...

=SUMIF(April!A:M,"Drahmee",April!C:O)

Sorry Rick,

Small change which confuses me... I'd like the formula to find the sum of all instances of the name referenced in cell A1 within the sheet named "Yearly, then insert the number two cells to the right into cell B2 on the sheet named "Yearly".

The only thing I know is that the FORMULA must be entered in cell B2 on the sheet named "Yearly"

Thanks again Rick!