Need values of multiple cells based on multiple other cells

**tridom** · 08-09-2012, 06:18 PM

Sorry about the title. I don't know how else to explain it.

I've attached a spreadsheet showing what I need to do.

Unfortunatly, I don't have the luxury of using a macro because they are blocked by our network, so I'm hoping someone can figure out a way to do this without me having to reformat the entire sheet. Its pretty extensive.

Example.xlsx

**martindwilson** · 08-09-2012, 07:19 PM

=SUMPRODUCT(--(ISNUMBER(SEARCH(" "&"cell"&" "," "&$A$2:$A$8&" "))),$C$2:$C$8)

**tridom** · 08-09-2012, 07:32 PM

Hi martindwilson,

Thanks for the reply, but I guess I didn't explain right.

Where my problem comes in, if you look at the attached spreadsheet, I need to sum the values of cells C1 and C5 based on the fact that the word "Cell" is in A2 and A6. The value is one row up from the found search term, not on the same row.

My apologies for not providing a better explanation.

**icestationzbra** · 08-09-2012, 09:55 PM

this is a very rudimentary approach, i hope someone helps you with a better solution:

**martindwilson** · 08-10-2012, 04:55 AM

offset the formula note there must be the same number of cells in both ranges a2:a8=7 c1:c7=7
=SUMPRODUCT(--(ISNUMBER(SEARCH(" "&"cell"&" "," "&$A$2:$A$8&" "))),$C$1:$C$7)
gives 24690

**tridom** · 08-10-2012, 06:48 AM

icestationzbra: Good thinking. It may be rudimentary, but it works

Thanks for giving me the idea though. I didn't think about using a true or false in another cell.

martindwilson: Your solution is a little cleaner and avoids me having to use another column. I wasn't aware I could offset the ranges. DUH.

Really do appreciate the assistance guys!