IFERROR with SUMIFS

[shiftyspina]

Hi all

can i add an iferror function to a sumifs formula?

Im trying to use a sum ifs formula to look at a set of data, based on two cell values, for instance if cell a1 = shop and cell a2 = chocolate in cell a3 it will tell me a figure like 0.094, but if cell a1 = shop and cell a2 = cereal but in my data im looking at there is no cereal, then i want cell a3 to equal 0.1.

if cell a1 doesnt = shop then cell a3 returns 0 which is correct i want it to do this.

my formula at the moment is

Please Login or Register  to view this content.

Please Login or Register  to view this content.

but i know somethings not quite right there?......

[codeslizer]

but if cell a1 = shop and cell a2 = cereal but in my data im looking at there is no cereal, then i want cell a3 to equal 0.1.

Am sorry, am unable to understand this part. Could you help us with a sample code of your work as well? - It'll be alot easier to get through with a slight explanation of what exactly you want. From here, it seems like this sheet contains the operands for SUMIFS function.

We need to know from where you're taking the data for operation and exactly where are you trying to perform the operation. For instance, if you'd like to add D1:D4 using SUMIFS() then where do put this formula and what all operands you take into consideration, like A column should be Shop and B column should be Chocolate.. and so on. Your worked file will be of great help!

[kvsrinivasamurthy]

Try this.Closing of bracket is changed.
=iferror(sumifs(Lookups!$K$500:$K$617,Lookups!$J$500:$J$617,A1,Lookups!$I$500:$I$617,A2),0.1)

[kvsrinivasamurthy]

Duplicated ,Sorry.

[shiftyspina]

test1.xlsx

Hi I have attached a sample of what i need to do, i also tried your above formula but it still doesnt put 0.1 as the value if it says shop but cant find the info in the data?.......

Thanks for getting back to me

[Jul Stev]

hi,

try this in A4

=IF(A1="shop",VLOOKUP(A2,Sheet2!$B$4:$C$13,2,0),0)

and drag it to the right, or there is other condition that u must use sumif?

[Jul Stev]

srry, forgot to wrap it with iferror

=IFERROR(IF(A1="shop",VLOOKUP(A2,Sheet2!$B$4:$C$13,2,0),0),0.1)

[JosephP]

looks like you want a lookup not a sum so
=IFERROR(LOOKUP(2,1/(Sheet2!$B$4:$B$13=D2)/(Sheet2!$A$4:$A$13=D1),Sheet2!$C$4:$C$13),0.1)

[shiftyspina]

Hi JosephP your formula returns 0.1 in those that should say 0 in the test attachment, and Jul Stev your formula tells me i have entered too many arguements.

Thanks for looking at this for me, maybe what i am trying to do is not possible?

[JosephP]

perhaps
=If(d1="shop",IFERROR(LOOKUP(2,1/(Sheet2!$B$4:$B$13=D2)/(Sheet2!$A$4:$A$13=D1),Sheet2!$C$4:$C$13),0.1),0)

[shiftyspina]

ah sussed both work, thank you both, i had originally missed the v out of vlookup part in Jul Stevs formula, and had looked at $a$4:$b$13 in JosephPs formula.

Thank you both very very much