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,cu n,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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Pete" wrote:
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,cu n,lim,ccs,zrh,or fra

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

Thanks,

Pete

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","om it")
-- do the sumif on the helper column if it equals "include"

Others may come up with better ideas.

--
Regards,
Fred


"Pete" wrote in message
...
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,cu n,lim,ccs,zrh,or fra

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

Thanks,

Pete

This is great. Works beautifully! Thank you so much!

"Max" wrote:

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Pete" wrote:
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,cu n,lim,ccs,zrh,or fra

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

Thanks,

Pete

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" wrote:
This is great. Works beautifully! Thank you so much!

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?
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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)

" wrote:

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.