=SUMPRODUCT((qry_FY06_COMMISSION_BUDGET_INFO!B2:B4
="07")*((qry_FY06_COMMISSION_BUDGET_INFO!H2:H4="AB C TRAVEL SERV OVERSEA
LTD")+(qry_FY06_COMMISSION_BUDGET_INFO!H2:H4="DEF TRAVEL SERVICES"
))*(qry_FY06_COMMISSION_BUDGET_INFO!O2:O4))


On Jan 26, 3:58 pm, dnorthcutt
wrote:
I tried your way from your previous post but it will not work.

Here is something that does work for just one criteria=ABC overseas

=SUMIF(qry_FY06_COMMISSION_BUDGET_INFO!P2:P30000," ABC TRAVEL SERV OVERSEA
LTD",qry_FY06_COMMISSION_BUDGET_INFO!O2:O30000)

but the following will not work:

=SUMIF(qry_FY06_COMMISSION_BUDGET_INFO!P2:P30000," ABC TRAVEL SERV OVERSEA
LTD" OR "DEF TRAVEL SERVICES",qry_FY06_COMMISSION_BUDGET_INFO!O2:O3000 0)

I need both ABC overseas travel and DEF travel services (vendors)to give me
a dollar total from column O. I used the 900 and 100 scenario just for
quicker typing.
Both ABC and DEF vendors are in column P. Really I have about 5 vendor
names that I have to add for column P. I figured giving you an example for 2
would work and I could just duplicate what you told me to do.

thanks,
darlene



"Jason Lepack" wrote:
Change:
qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000=100

To:
or(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000=100,
qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000=900,
qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000=300)

On Jan 26, 12:43 pm, dnorthcutt
wrote:
Jason
Me again.........
You have no idea how you have helped me, thank you so very much. It all
finally worked. Now if you are game I have another request. How would I
write the formula if I wanted to pick out more than one certain data in the
same colum e.g., before I needed all the 100's in column H but now I want all
the 100's, 900's and the 300's in column H, not just the 100's. I have
worked and worked with the formula you gave me for just the 100's but I keep
getting various error messages no matter how I change it. I guess was hoping
that I could chance my way to the correct answer but that just does'nt seem
to be working.............lol. Again if you can help me great, if I am
getting bothersome just let me know.....lol.

thanks,
darlene

"Jason Lepack" wrote:
No, I don't check that at work.

I'm located near Ottawa, Canada.

Here is my formula:
=SUMPRODUCT(--(qry_FY06_COMMISSION_BUDGET_INFO!B2:B30000 ="'0
7")*--(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000=100)*(q ry_FY06_COMMISSION_***BUDGET_INFO!O2:O30000))

Take out the leading apostrophe in the "'07" in your formula. The ' is
not a part of the string, it's a text qualifier.

If that doesn't fix it then try below.

In four seperate cells place these four fomulas:
=SUMPRODUCT(--(qry_FY06_COMMISSION_BUDGET_INFO!B2:B30000 ="'07"))
=SUMPRODUCT(--(qry_FY06_COMMISSION_BUDGET_INFO!B2:B30000 =07))

=SUMPRODUCT(--(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000=100))
=SUMPRODUCT(--(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000="100"))

The criteria at the end of the statements that return numbers other
than zero are the two criteria that you need in your formula.

Cheers,
Jason Lepack
On Jan 25, 9:51 am, dnorthcutt
wrote:
Jason,

Will you be able to answer during the day from you gmail account? If you
can see emails during the day I will switch and email you at gmail. If not,
here is what I wanted to write to you:

Column B looks to be text, it is a number but has the green corner. Column
H is also a number but has the green corner, and column O is the number, no
green corner.
Results needed:

I need the dollar sum of column O, by all the 100's (budget code) in column
H, by the column B (the month-07).

Here is my formula:
=SUMPRODUCT(--(qry_FY06_COMMISSION_BUDGET_INFO!B2:B30000 ="'0
7")*--(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000=100)*(q ry_FY06_COMMISSION_***BUDGET_INFO!O2:O30000))

Again, thanks so much for all your help. BTW where are you located e..g.,
US, Ohio England, just curious.

thanks,
darlene

"Jason Lepack" wrote:
For sure, email me at the address in my profile.

First glance makes me wonder how your Columns are formatted.
I have assumed that column B is formatted as text and that H was
formatted as a number.

I'll be home in an hour.

Cheers,
Jason Lepack

On Jan 24, 4:21 pm, dnorthcutt
wrote:
Hi Jason

Can we talk direct via email without having to go through this online
discussion. That would really help formula errors communication.
Just let me know.

thanks,
darlene

"Jason Lepack" wrote:
Sumif doesn't work for multiple conditions, but you can use sumproduct.

=SUMPRODUCT(--(qry_FY06_COMMISSION_BUDGET_INFO!B2:B30000 =
"07")*--(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000=900)*(q ry_FY06_COMMISSIO****N_BUDGET_INFO!O2:O30000))

Remember that the above formula will wrap, copy it all.

Cheers,
Jason Lepack

On Jan 24, 11:28 am, dnorthcutt
wrote:
Jason Lepack,

Thank you so much for that info, it worked. Now I tried to add something to
the sumiff but it is giving me a compile error=expected:line number,or label,
or statement or end of statement.
Here is what I put in originall and it worked:

=SUMIF(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000,9 00,qry_FY06_COMMISSION_BU*****DGET_INFO!O2:O30000)
this is giving me the sum for all 900 entries

Now I tried to add another parameter and it gives me the above error.
=SUMIF(qry_FY06_COMMISSION_BUDGET_INFO!H2:H30000,9 00,qry_FY06_COMMISSION_BU*****DGET_INFO!B2:B30000, 07,qry_FY06_COMMISSION_BUDGET_INFO!O2:O30000)

qry_FY06_COMMISSION_BUDGET_INFO!B2:B30000,07 was added as another
paramenter. I needed all 900 amounts that were 07

Any help you can give me in this is greatly appreciated.

"Jason Lepack" wrote:
All you need is sumif

=sumif(sheetname!rangewiththe900s,900,sheetname!ra ngethatyouwanttoadd)

Cheers,
Jason Lepack
dnorthcutt wrote:
I have taken a qry from access and exported it into excel and am now trying
to come up with a formula in excel. I have 3 sheets in my workbook and I am
trying to come up with a one cell sum from the data in one of the other
sheets. My conditions are if this column in sheet a=(this sheet has a very
long name) has a 900 in it then take the figure from this other column in
sheet a and sum it into this column in sheet b. There are many figures in
one sheet and I want it to sum into a cell in another sheet. Please help- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -