Formula not working

[rameshppc]

Hi I am using below formula in a cell,

F4 =(IF(AND(B4="MONEY TRANSFER",E4="1"),SUMIFS($R$4:$R$8,$P$4:$P$8,"<="&D4,$Q$4:$Q$8,">="&D4),IF(AND(B4="MONEY TRANSFER",E4="11"),SUMIFS($R$4:$R$8,$P$4:$P$8,"<="&D4,$Q$4:$Q$8,">="&D4)+3.5,IF(AND(B4="MONEY TRANSFER",E4="10"),SUMIFS($N$4:$N$28,$L$4:$L$28,"<="&D4,$M$4:$M$28,">="&D4)+3.5,IF(AND(B4="MONEY TRANSFER",E4=""),SUMIFS($N$4:$N$28,$L$4:$L$28,"<="&D4,$M$4:$M$28,">="&D4),"0"))

(VALUE EXPECTED FROM THE SUM RANGE $N$4:$N$28)

But result shows "0"

B4 = MONEY TRANSFER
E4 = 10

I don't know where i did the wrong.

Also pls help me is it possible to simplify...

 

[AlphaFrog]

Re: Formula not working / posiible to simplify

Don't put the numeric values in quotes

=IF(B4="MONEY TRANSFER",
IF(E4=1,SUMIFS($R$4:$R$8,$P$4:$P$8,"<="&D4,$Q$4:$Q$8,">="&D4),
IF(E4=11,SUMIFS($R$4:$R$8,$P$4:$P$8,"<="&D4,$Q$4:$Q$8,">="&D4)+3.5,
IF(E4=10,SUMIFS($N$4:$N$28,$L$4:$L$28,"<="&D4,$M$4:$M$28,">="&D4)+3.5,
IF(E4="",SUMIFS($N$4:$N$28,$L$4:$L$28,"<="&D4,$M$4:$M$28,">="&D4),0)))),0)

 

[JustynaMK]

Re: Formula not working / posiible to simplify

Just to add up to AlphaFrog's solution, one possible simplification is to combine CHOOSE & MATCH functions. The ultimate goal is to create the following structure:

=IFERROR(IF(B4="MONEY TRANSFER",CHOOSE(MATCH(E4,{1,11,10,0},0),"sumifs E4=1","sumifs E4=11","sumifs E4=10","sumifs E4 is empty"),""),"")

When replaced with your SUMIFS functions, the final formula will look like that:

=IFERROR(IF(B4="MONEY TRANSFER",CHOOSE(MATCH(E4,{1,11,10,0},0),SUMIFS($R$4:$R$8,$P$4:$P$8,"<="&D4,$Q$4:$Q$8,">="&D4),SUMIFS($R$4:$R$8,$P$4:$P$8,"<="&D4,$Q$4:$Q$8,">="&D4)+3.5,SUMIFS($N$4:$N$28,$L$4:$L$28,"<="&D4,$M$4:$M$28,">="&D4)+3.5,SUMIFS($N$4:$N$28,$L$4:$L$28,"<="&D4,$M$4:$M$28,">="&D4)),""),"")

 

[rameshppc]

Re: Formula not working / posiible to simplify

Don't put the numeric values in quotes

=IF(B4="MONEY TRANSFER",
IF(E4=1,SUMIFS($R$4:$R$8,$P$4:$P$8,"<="&D4,$Q$4:$Q$8,">="&D4),
IF(E4=11,SUMIFS($R$4:$R$8,$P$4:$P$8,"<="&D4,$Q$4:$Q$8,">="&D4)+3.5,
IF(E4=10,SUMIFS($N$4:$N$28,$L$4:$L$28,"<="&D4,$M$4:$M$28,">="&D4)+3.5,
IF(E4="",SUMIFS($N$4:$N$28,$L$4:$L$28,"<="&D4,$M$4:$M$28,">="&D4),0)))),0)

hi.. sorry...

i will follow your instruction from my next message...

 

[rameshppc]

Re: Formula not working / posiible to simplify

tks a lot its working..

 

[rameshppc]

Re: Formula not working / posiible to simplify

Hi justy... one of my subscribed thread is pending response.. can you pls check to help me..