Complex Sumif
 

I'm not sure sumif is the correct use.

I have a table (table1) which contains the following:
Column C= a cost center
Column G = $$$$
Column J = Month of year

I need to sum all the qualifying amounts in column G if they equal the same
cost_center in Column C AND are in the same month in Column J.

Will Sumif work here? If not what can I use?

Thanks for helping,

El Bee

Complex Sumif
 

Will Sumif work here?

No.

Column J = Month of year

Is that the month name as a TEXT string like June?

Try this:

Use cells to hold your criteria:

A1 = cost center
B1 = month name as a TEXT string = June

=SUMPRODUCT(--(C1:C100=A1),--(J1:J100=B1),G1:G100)


--
Biff
Microsoft Excel MVP


"El Bee" wrote in message
...
I'm not sure sumif is the correct use.

I have a table (table1) which contains the following:
Column C= a cost center
Column G = $$$$
Column J = Month of year

I need to sum all the qualifying amounts in column G if they equal the
same
cost_center in Column C AND are in the same month in Column J.

Will Sumif work here? If not what can I use?

Thanks for helping,

El Bee

Complex Sumif
 

hi
sumif is good only for 1 criteria. sumproduct would do the same and works
with more than one criterial.
a example based on your example.....
=SUMPRODUCT((C2:C100="cost center")*(G2:G100)*(J2:J100="march"))

if you have never worked with sumproduct, see this site for more info
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

regards
FSt1


"El Bee" wrote:

I'm not sure sumif is the correct use.

I have a table (table1) which contains the following:
Column C= a cost center
Column G = $$$$
Column J = Month of year

I need to sum all the qualifying amounts in column G if they equal the same
cost_center in Column C AND are in the same month in Column J.

Will Sumif work here? If not what can I use?

Thanks for helping,

El Bee

Complex Sumif
 

I found this formula to work, sometimes!
In my spreadsheet I have 4 Names: Cost_Center, Acct_nbr, Mth, and Amt.

These names point to different columns on a seperat worksheet. I used the
forumla help menu option to build a Sumproduct array and with each array I
entered it would display the resuts; correctly but when I closed the window
and the formula entered into the cell I get the #N/A error.

Here's the formula
=SUMPRODUCT((Acct=660000)*(Cost_Center="1170-17008")*(Mth="Mar"),Amt)
There is 2 records and it has the following values:
Record #1:
Acct= 660000
Cost_Center = "1170-17008"
Mth = "Mar"
Amt = $20.75

Record #2:
Acct= 660000
Cost_Center = "1170-17008"
Mth = "Apr"
Amt = $750.00

According to the formula I should see a value of $20.75 but I get #N/A
instead.

Not sure why this is happening



"FSt1" wrote:

hi
sumif is good only for 1 criteria. sumproduct would do the same and works
with more than one criterial.
a example based on your example.....
=SUMPRODUCT((C2:C100="cost center")*(G2:G100)*(J2:J100="march"))

if you have never worked with sumproduct, see this site for more info
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

regards
FSt1


"El Bee" wrote:

I'm not sure sumif is the correct use.

I have a table (table1) which contains the following:
Column C= a cost center
Column G = $$$$
Column J = Month of year

I need to sum all the qualifying amounts in column G if they equal the same
cost_center in Column C AND are in the same month in Column J.

Will Sumif work here? If not what can I use?

Thanks for helping,

El Bee

Complex Sumif
 

UPDATE:

I discovered that one of my Name Definitions did not contain the same number
of rows as the rest of them. I fixed this problem and now I get #VALUE in
the cell.

Nothing else has changed.


"El Bee" wrote:

I found this formula to work, sometimes!
In my spreadsheet I have 4 Names: Cost_Center, Acct_nbr, Mth, and Amt.

These names point to different columns on a seperat worksheet. I used the
forumla help menu option to build a Sumproduct array and with each array I
entered it would display the resuts; correctly but when I closed the window
and the formula entered into the cell I get the #N/A error.

Here's the formula
=SUMPRODUCT((Acct=660000)*(Cost_Center="1170-17008")*(Mth="Mar"),Amt)
There is 2 records and it has the following values:
Record #1:
Acct= 660000
Cost_Center = "1170-17008"
Mth = "Mar"
Amt = $20.75

Record #2:
Acct= 660000
Cost_Center = "1170-17008"
Mth = "Apr"
Amt = $750.00

According to the formula I should see a value of $20.75 but I get #N/A
instead.

Not sure why this is happening



"FSt1" wrote:

hi
sumif is good only for 1 criteria. sumproduct would do the same and works
with more than one criterial.
a example based on your example.....
=SUMPRODUCT((C2:C100="cost center")*(G2:G100)*(J2:J100="march"))

if you have never worked with sumproduct, see this site for more info
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

regards
FSt1


"El Bee" wrote:

I'm not sure sumif is the correct use.

I have a table (table1) which contains the following:
Column C= a cost center
Column G = $$$$
Column J = Month of year

I need to sum all the qualifying amounts in column G if they equal the same
cost_center in Column C AND are in the same month in Column J.

Will Sumif work here? If not what can I use?

Thanks for helping,

El Bee

Complex Sumif
 

Stop The Presses!!

It now works; turns out there was another names definition that was missing
the first cell in the array which caused the error.

Thanks for the initial help and the web site. It's been a slow learning
process.

"T. Valko" wrote:

Will Sumif work here?

No.

Column J = Month of year

Is that the month name as a TEXT string like June?

Try this:

Use cells to hold your criteria:

A1 = cost center
B1 = month name as a TEXT string = June

=SUMPRODUCT(--(C1:C100=A1),--(J1:J100=B1),G1:G100)


--
Biff
Microsoft Excel MVP


"El Bee" wrote in message
...
I'm not sure sumif is the correct use.

I have a table (table1) which contains the following:
Column C= a cost center
Column G = $$$$
Column J = Month of year

I need to sum all the qualifying amounts in column G if they equal the
same
cost_center in Column C AND are in the same month in Column J.

Will Sumif work here? If not what can I use?

Thanks for helping,

El Bee

Complex Sumif
 

Good deal. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"El Bee" wrote in message
...
Stop The Presses!!

It now works; turns out there was another names definition that was
missing
the first cell in the array which caused the error.

Thanks for the initial help and the web site. It's been a slow learning
process.

"T. Valko" wrote:

Will Sumif work here?

No.

Column J = Month of year

Is that the month name as a TEXT string like June?

Try this:

Use cells to hold your criteria:

A1 = cost center
B1 = month name as a TEXT string = June

=SUMPRODUCT(--(C1:C100=A1),--(J1:J100=B1),G1:G100)


--
Biff
Microsoft Excel MVP


"El Bee" wrote in message
...
I'm not sure sumif is the correct use.

I have a table (table1) which contains the following:
Column C= a cost center
Column G = $$$$
Column J = Month of year

I need to sum all the qualifying amounts in column G if they equal the
same
cost_center in Column C AND are in the same month in Column J.

Will Sumif work here? If not what can I use?

Thanks for helping,

El Bee