Formula not working

rameshppc

Board Regular
Joined
Jun 10, 2017
Messages
97
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...
 

Some videos you may like

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
Joined
Sep 2, 2009
Messages
16,261
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
Joined
Aug 28, 2016
Messages
628
Office Version
365, 2013
Platform
Windows
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
Joined
Jun 10, 2017
Messages
97
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

Board Regular
Joined
Jun 10, 2017
Messages
97
Re: Formula not working / posiible to simplify

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

Watch MrExcel Video

Forum statistics

Threads
1,095,389
Messages
5,444,195
Members
405,273
Latest member
cswshaun

This Week's Hot Topics

Top