Consolidate Multiple IF Cell Contains Sum Formula

[lsab]

I've created a formula to check if a cell contains "3". If it does contain "3" then it returns the value of a cell located 2 rows down.
I've then duplicated this formula using + so it can add the values of all the cells found that contain "3".

The formula is:
=IF(ISNUMBER(SEARCH("3",'Block 1'!E9)),'Block 1'!E11,)+IF(ISNUMBER(SEARCH("3",'Block 1'!E13)),'Block 1'!E15,)+IF(ISNUMBER(SEARCH("3",'Block 1'!E17)),'Block 1'!E19,)+IF(ISNUMBER(SEARCH("3",'Block 1'!E21)),'Block 1'!E23,)

I need to consolidate this as currently it only searches 4 cells, I in fact need to to search and add 55 cells.

Is it possible to consolidate this formula into something a little more elegant?


Many Thanks.

 

[XOR LX]

Hi,

I'm assuming that the entries in the cells in between those which you wish to consider may also contain e.g. "3", so that we need to restrict the formula to looking at every fourth cell only (otherwise the solution would be simplified somewhat):

=SUMPRODUCT((ISNUMBER(SEARCH("3",T(OFFSET('Block 1'!E9,4*(ROW($1:$100)-1),,,))))*N(OFFSET('Block 1'!E11,4*(ROW($1:$100)-1),,,))))

Change the $100 to a sufficiently higher number if required (as it stands, the formula will suffice up to cell E407).

Note also that row insertions within this range would compromise this formula. Let me know if that is something which you would like to have the option of doing and I will tweak the formula accordingly.

Regards

 

[lsab]

That is utterly amazing! Thanks very much!

 

[XOR LX]

No worries! Glad I could help.

Cheers

 

[Aladin Akyurek]

I've created a formula to check if a cell contains "3". If it does contain "3" then it returns the value of a cell located 2 rows down.
I've then duplicated this formula using + so it can add the values of all the cells found that contain "3".

The formula is:
=IF(ISNUMBER(SEARCH("3",'Block 1'!E9)),'Block 1'!E11,)+IF(ISNUMBER(SEARCH("3",'Block 1'!E13)),'Block 1'!E15,)+IF(ISNUMBER(SEARCH("3",'Block 1'!E17)),'Block 1'!E19,)+IF(ISNUMBER(SEARCH("3",'Block 1'!E21)),'Block 1'!E23,)

I need to consolidate this as currently it only searches 4 cells, I in fact need to to search and add 55 cells.

Is it possible to consolidate this formula into something a little more elegant?


Many Thanks.

Just enter:

=SUMPRODUCT(
  --(MOD(ROW('Block 1'!E9:E27)-ROW('Block 1'!E9),4)=0),
  --ISNUMBER(SEARCH(3,'Block 1'!E9:E27)),'Block 1'!E11:E29)

Note that 2 cell difference between E9:E27 and E11:E29.