Home |
Search |
Today's Posts |
#1
|
|||
|
|||
sumproduct function returns #value or #ref error
hi all,
I'm trying to make a summary sheet for my inventory sheet that will tell me the total #of product (types a, b, c...) produced in a certain month. My inventory sheet looks something like this: Month Product 1 a 1 a 1 b 2 a 3 a 3 c For my summary, say I want to enter the month of interest in cell c1. So, if I'm interested in month 1, the cell in which my formula is in will spit out 3 as the total number of product produced. I tried both =sumproduct((month(b2:b6)=c1)*1) and =sumproduct((a2:a6(b2:b6)=c1)*1) which give me #value and #ref errors, respectively. What am I doing wrong? Is there another formula I could try? Thanks in advance. |
#2
|
|||
|
|||
Hi Jennie
Take a look at the COUNTIF() formula. -- Best Regards Leo Heuser Followup to newsgroup only please. "Jennie" skrev i en meddelelse ... hi all, I'm trying to make a summary sheet for my inventory sheet that will tell me the total #of product (types a, b, c...) produced in a certain month. My inventory sheet looks something like this: Month Product 1 a 1 a 1 b 2 a 3 a 3 c For my summary, say I want to enter the month of interest in cell c1. So, if I'm interested in month 1, the cell in which my formula is in will spit out 3 as the total number of product produced. I tried both =sumproduct((month(b2:b6)=c1)*1) and =sumproduct((a2:a6(b2:b6)=c1)*1) which give me #value and #ref errors, respectively. What am I doing wrong? Is there another formula I could try? Thanks in advance. |
#3
|
|||
|
|||
sorry leo, your post is not showing up
"Jennie" wrote: hi all, I'm trying to make a summary sheet for my inventory sheet that will tell me the total #of product (types a, b, c...) produced in a certain month. My inventory sheet looks something like this: Month Product 1 a 1 a 1 b 2 a 3 a 3 c For my summary, say I want to enter the month of interest in cell c1. So, if I'm interested in month 1, the cell in which my formula is in will spit out 3 as the total number of product produced. I tried both =sumproduct((month(b2:b6)=c1)*1) and =sumproduct((a2:a6(b2:b6)=c1)*1) which give me #value and #ref errors, respectively. What am I doing wrong? Is there another formula I could try? Thanks in advance. |
#4
|
|||
|
|||
I don't know how to make the countif formula work if I have 3 parameters. It
only works with 2. I already tried something like countif(a2:a6=c1,b2:b6) and the program doesn't understand this command. Any other suggestions, please????? "Leo Heuser" wrote: Hi Jennie Take a look at the COUNTIF() formula. -- Best Regards Leo Heuser Followup to newsgroup only please. "Jennie" skrev i en meddelelse ... hi all, I'm trying to make a summary sheet for my inventory sheet that will tell me the total #of product (types a, b, c...) produced in a certain month. My inventory sheet looks something like this: Month Product 1 a 1 a 1 b 2 a 3 a 3 c For my summary, say I want to enter the month of interest in cell c1. So, if I'm interested in month 1, the cell in which my formula is in will spit out 3 as the total number of product produced. I tried both =sumproduct((month(b2:b6)=c1)*1) and =sumproduct((a2:a6(b2:b6)=c1)*1) which give me #value and #ref errors, respectively. What am I doing wrong? Is there another formula I could try? Thanks in advance. |
#5
|
|||
|
|||
What exactly are you trying to count? Your first
posting, with a solution of 3, apparently just counted the number of ones in column A. Do you want the number of a's(2), b's(1) and c's(0) for the month in C1 Is this the formula, you are looking for: =SUMPRODUCT((A1:A100=C1)*(B1:B100={"a","b","c"})) -- Best Regards Leo Heuser Followup to newsgroup only please. "Jennie" skrev i en meddelelse ... I don't know how to make the countif formula work if I have 3 parameters. It only works with 2. I already tried something like countif(a2:a6=c1,b2:b6) and the program doesn't understand this command. Any other suggestions, please????? "Leo Heuser" wrote: Hi Jennie Take a look at the COUNTIF() formula. -- Best Regards Leo Heuser Followup to newsgroup only please. "Jennie" skrev i en meddelelse ... hi all, I'm trying to make a summary sheet for my inventory sheet that will tell me the total #of product (types a, b, c...) produced in a certain month. My inventory sheet looks something like this: Month Product 1 a 1 a 1 b 2 a 3 a 3 c For my summary, say I want to enter the month of interest in cell c1. So, if I'm interested in month 1, the cell in which my formula is in will spit out 3 as the total number of product produced. I tried both =sumproduct((month(b2:b6)=c1)*1) and =sumproduct((a2:a6(b2:b6)=c1)*1) which give me #value and #ref errors, respectively. What am I doing wrong? Is there another formula I could try? Thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
weeknum function returns name error | Excel Worksheet Functions | |||
Automatically up date time in a cell | Excel Discussion (Misc queries) | |||
Why do I get a #Name error when I use the DateDif function? | Excel Worksheet Functions | |||
Match Function - no room for error | Excel Worksheet Functions | |||
Formula to list unique values | Excel Worksheet Functions |