help please - trouble with sumproduct function

[Jennie]

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.

 

[bj]

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.

 

[SongBear]

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),--(D1621 = $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