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

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

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_BUDGET_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_BUDGET_INFO!B2:B30000,07,qr y_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

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_COMMISSION_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,q ry_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 -

Okay I copied the formual below and it takes it but it does not give me the
amount. The amount is in the O2:O30000 column. Am I doing something wrong.
It is giving me a zero or an O value.

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_COMMISSION_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 -

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_COMMISSION_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 -

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 -