Help with sumif formula please

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to total large column of data, but I need to omit the rowif certain
letters appear in that row.

sumif column A does not equal one of these:
muc,str,cph,mad,bcn,lgw,dub,ams,eze,gru,gig,mex,cun,lim,ccs,zrh,or fra

Can anyone tell me how to accomplish this formula? I am desperate.

Thanks,

Pete
 
Assume the exceptions* are listed in E1:E17
*muc,str,cph,mad,bcn,...

Assuming col B is to be summed where the exceptions are not within col A
you could try this in say, C2:
=SUMPRODUCT((ISERROR(MATCH(A2:A100,E1:E17,0)))*(A2:A100<>""),B2:B100)
Adapt the ranges to suit
 
If this was me, I would do the following:

-- insert a helper column
-- use a Vlookup formula to determine if the cell is in the list
(=if(isna(vlookup(a1,range,1,false)),"include","omit")
-- do the sumif on the helper column if it equals "include"

Others may come up with better ideas.
 
Think you would be better off using DSUM.

DSUM(A:B, "Title from Column B", "Exclusion Criteria")

Title From Column B would be your sum field.

Exclusion Criteria would be in E1:U2, E1:U1 would be "Title from
Column B" and E2:U2 would be:

E2 <>"muc"
F2 <>"str"
G2....

So on and so forth.
 
Pete said:
This is great. Works beautifully! Thank you so much!
Click to expand...

Welcome, Pete. Think you forgot to click the "Yes"* button to the response,
though. Never mind, you can drop by & click it on your next visit. Cheers.
*to the Q: Was this post helpful to you?

---
 
I am not able to get this working. I read the help section on this and I
think I understand what needs to be done, but the only thing I can get it to
do is show a zero sum.

This is my formula:

DSUM(A1:G1555, “DISCOUNTâ€, L1:Z2)


The data to be summed is under column "DISCOUNT" in Column G, L1 is column
heading "DISCOUNT" and L:Z@2 is "<>Mun" "<>STR" etc. (no quotes in the l2:Z
row)
 
Back
Top