# Formula not working

#### rameshppc

##### Board Regular
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...

### Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

#### AlphaFrog

##### MrExcel MVP
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

##### Well-known Member
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:
Code:
``=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

##### Board Regular
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...

#### rameshppc

##### Board Regular
Re: Formula not working / posiible to simplify

tks a lot its working..

#### rameshppc

##### Board Regular
Re: Formula not working / posiible to simplify

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