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

  • Thread starter Thread starter thomas
  • Start date Start date
T

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!D3:D1000 )),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!D3:D1000 )),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
 
hi,

can you show us the worksheet function "FILTER" ?

isabelle

Le 2014-11-24 20:22, (e-mail address removed) a écrit :
 
(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!D3:D1000 )),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!D3:D1000 )),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
Click to expand...

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 C3:D3 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).
 
Back
Top