Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Complex Sumif | Excel Worksheet Functions | |||
Complex VBA Sumif | Excel Discussion (Misc queries) | |||
complex SUMIF | Excel Worksheet Functions | |||
Complex SUMIF/COUNT IF | Excel Discussion (Misc queries) | |||
Complex SUMIF | Excel Discussion (Misc queries) |