A More efficient way to have a function with multiple SUMIF functions.

[thomas]

I am using a function with many functions and am worried because it leaves a lot of room for possible error. Is there some other way that I could make this function more efficient to create less room for error?

Function:
=SUM(IFERROR(FILTER( TTS!A3:A1000 , SEARCH( "sphere" , TTS!B3:B1000 )),0),IFERROR(FILTER( TTS!C3:C1000 , SEARCH( "sphere" , TTS!D31000 )),0),IFERROR(FILTER( TTS!E3:E1000 , SEARCH( "sphere" , TTS!F3:F1000 )),0),IFERROR(FILTER( TTS!G3:G1000 , SEARCH( "sphere" , TTS!H3:H1000 )),0),IFERROR(FILTER( TTS!I3:I1000 , SEARCH( "sphere" , TTS!J3:J1000 )),0),IFERROR(FILTER( TTS!K3:K1000 , SEARCH( "sphere" , TTS!L3:L1000 )),0),IFERROR(FILTER( TTS!M3:M1000 , SEARCH( "sphere" , TTS!N3:N1000 )),0),IFERROR(FILTER( TTS!A3:A1000 , SEARCH( "referral" , TTS!B3:B1000 )),0),IFERROR(FILTER( TTS!C3:C1000 , SEARCH( "referral" , TTS!D31000 )),0),IFERROR(FILTER( TTS!E3:E1000 , SEARCH( "referral" , TTS!F3:F1000 )),0),IFERROR(FILTER( TTS!G3:G1000 , SEARCH( "referral" , TTS!H3:H1000 )),0),IFERROR(FILTER( TTS!I3:I1000 , SEARCH( "referral" ,TTS!J3:J1000 )),0),IFERROR(FILTER( TTS!K3:K1000 , SEARCH( "referral" , TTS!L3:L1000 )),0),IFERROR(FILTER( TTS!M3:M1000 , SEARCH( "referral" , TTS!N3:N1000 )),0))-G17

 

[isabelle]

hi,

can you show us the worksheet function "FILTER" ?

isabelle

Le 2014-11-24 20:22, (e-mail address removed) a écrit :

 

[Alex Plantema]

(e-mail address removed) schreef in

I am using a function with many functions and am worried because it
leaves a lot of room for possible error. Is there some other way
that I could make this function more efficient to create less room
for error?

Function:
=SUM(
IFERROR(FILTER( TTS!A3:A1000 , SEARCH( "sphere" , TTS!B3:B1000 )),0),
IFERROR(FILTER( TTS!C3:C1000 , SEARCH( "sphere" , TTS!D31000 )),0),
IFERROR(FILTER( TTS!E3:E1000 , SEARCH( "sphere" , TTS!F3:F1000 )),0),
IFERROR(FILTER( TTS!G3:G1000 , SEARCH( "sphere" , TTS!H3:H1000 )),0),
IFERROR(FILTER( TTS!I3:I1000 , SEARCH( "sphere" , TTS!J3:J1000 )),0),
IFERROR(FILTER( TTS!K3:K1000 , SEARCH( "sphere" , TTS!L3:L1000 )),0),
IFERROR(FILTER( TTS!M3:M1000 , SEARCH( "sphere" , TTS!N3:N1000 )),0),

IFERROR(FILTER( TTS!A3:A1000 , SEARCH( "referral" , TTS!B3:B1000 )),0),
IFERROR(FILTER( TTS!C3:C1000 , SEARCH( "referral" , TTS!D31000 )),0),
IFERROR(FILTER( TTS!E3:E1000 , SEARCH( "referral" , TTS!F3:F1000 )),0),
IFERROR(FILTER( TTS!G3:G1000 , SEARCH( "referral" , TTS!H3:H1000 )),0),
IFERROR(FILTER( TTS!I3:I1000 , SEARCH( "referral" , TTS!J3:J1000 )),0),
IFERROR(FILTER( TTS!K3:K1000 , SEARCH( "referral" , TTS!L3:L1000 )),0),
IFERROR(FILTER( TTS!M3:M1000 , SEARCH( "referral" , TTS!N3:N1000 )),0)
)-G17

The way you arranged your data, the same type of data in more than one column, makes it difficult to use standard tools.
If you move C33 to A4:B4, E3:F3 to A5:B5 etc. you can use an array formula like
{=SUM(IFERROR(FILTER(TTS!A3:A9,SEARCH("sphere"),TTS!B3:B9)),0),IFERROR(FILTER(TTS!A3:A9,SEARCH("referral",TTS!B3:B9)),0))-G17}
(Enter without the {} but use Ctrl+Shift+Enter).