Home |
Search |
Today's Posts |
#1
|
|||
|
|||
help please - trouble with sumproduct function
hi all,
I'm trying to make a monthly summary sheet of inventory but don't know how to fit multiple criteria within a statement. I tried entering =sumproduct(--(monthrange=$C$1),--(typerange = $A2)) in the cell next to part type a, but got a #NAME? error. How does this formula take into account the different types (a,b,c) of parts and what am I doing wrong?? For one product my first column of data is the month # (1, 2, 3..), second column is part type (type a, type b, type c, ...). On my summary worksheet for this product I list the part types in consecutive rows and I'd like to have a formula that links the summary worksheet to my inventory sheet so that when I type in a specific month I'm interested in, the number of type a, type b, and type c parts used during that month will display. Example Inventory sheet: Month Type 1 a 1 a 1 b 2 a 3 a 3 c Example Summary sheet: Type specific month in cell C1: (e.g. 1) #type a: 2 #type b: 1 #type c: 0 Please respond with example formulas. Thanks in advance. |
#2
|
|||
|
|||
What did you enter for the monthrange and the type range?
if you entered these as stated, you would get a "Name" error if say they are in sheet 1 with the months in column A and the Parts in column B with 100 rows of data and your summery information is in sheet tow with the individual parts list being in Column A starting row two, while the months owuld be in row 1 starting in Column B in Cell B2 =sumproduct(--(Sheet1!$A$1:$A$100=$C$1),--(Sheet1!$B$1:$B$100 = $A2)) copy this equation down for all of the products and across for all of the months. "Jennie" wrote: hi all, I'm trying to make a monthly summary sheet of inventory but don't know how to fit multiple criteria within a statement. I tried entering =sumproduct(--(monthrange=$C$1),--(typerange = $A2)) in the cell next to part type a, but got a #NAME? error. How does this formula take into account the different types (a,b,c) of parts and what am I doing wrong?? For one product my first column of data is the month # (1, 2, 3..), second column is part type (type a, type b, type c, ...). On my summary worksheet for this product I list the part types in consecutive rows and I'd like to have a formula that links the summary worksheet to my inventory sheet so that when I type in a specific month I'm interested in, the number of type a, type b, and type c parts used during that month will display. Example Inventory sheet: Month Type 1 a 1 a 1 b 2 a 3 a 3 c Example Summary sheet: Type specific month in cell C1: (e.g. 1) #type a: 2 #type b: 1 #type c: 0 Please respond with example formulas. Thanks in advance. |
#3
|
|||
|
|||
Jennie
I was working on a reply using the conditional sum wizard and before I posted, checked to see if your question had been answered. I noted that BJ had given you the sumproduct formula. I tried it out and got, as expected, an error. You have to substitute the actual range for the words monthrange and typerange. Excel is looking in the named ranges list and not finding those names. the formula might look something like below. the ranges below represent where I pasted the stuff from your question into my sheet to try stuff on it. =SUMPRODUCT(--(C16:C21=$C$1),--(D16:D21 = $A2)) the alternative is to highlight the cells where the month numbers are stored in the inventory data sheet and name that range monthrange, then do the same to the data cells for the part type data, only typerange. then the formula would work as written. um. pretty sure, let me check. lol. Yes, i kept the formula as it was given to you by bj, then went to the cells where i stored the test data and named the ranges. a number immediately replaced "name?" in the cell. to name a range, highlight the cells with one type of data, like the months, go to the insert menu at the top, then name/define. In the dialog box, type "monthrange" in the top horizontal field., then click OK. do the same for the typerange. Formula should work. Hope this helps SongBear "Jennie" wrote: hi all, I'm trying to make a monthly summary sheet of inventory but don't know how to fit multiple criteria within a statement. I tried entering =sumproduct(--(monthrange=$C$1),--(typerange = $A2)) in the cell next to part type a, but got a #NAME? error. How does this formula take into account the different types (a,b,c) of parts and what am I doing wrong?? For one product my first column of data is the month # (1, 2, 3..), second column is part type (type a, type b, type c, ...). On my summary worksheet for this product I list the part types in consecutive rows and I'd like to have a formula that links the summary worksheet to my inventory sheet so that when I type in a specific month I'm interested in, the number of type a, type b, and type c parts used during that month will display. Example Inventory sheet: Month Type 1 a 1 a 1 b 2 a 3 a 3 c Example Summary sheet: Type specific month in cell C1: (e.g. 1) #type a: 2 #type b: 1 #type c: 0 Please respond with example formulas. Thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date & Time | New Users to Excel | |||
Automatically up date time in a cell | Excel Discussion (Misc queries) | |||
clock | Excel Worksheet Functions | |||
sumproduct function | Excel Worksheet Functions | |||
Sumproduct function not working | Excel Worksheet Functions |