Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Formula
Hi
I wonder if anyone can help me with the following problem. I have two tabs within a worksheet. The second has a number of colums including those shown below: Name Date Value Smith 11/12 N Jones 11/05 D Smith 11/20 D Smith 11/08 N I want to display (on the first tab) a total of how many times the Name = Smith and the Value = N, which should return a value of 2 from the example above. However, I can't get anything to work. Any help would really be appreciated -- Stormin |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Formula
Hi,
First a bit of terminology. You have worksheets in a workbook and not tabs in a worksheet. A worksheet has one tab and it displays the name of the worksheet and provide a few bits of functionality. Having got that right try this =SUMPRODUCT((Sheet2!A2:A5="Smith")*(Sheet2!C2:C5=" N")) Mike "Stormin" wrote: Hi I wonder if anyone can help me with the following problem. I have two tabs within a worksheet. The second has a number of colums including those shown below: Name Date Value Smith 11/12 N Jones 11/05 D Smith 11/20 D Smith 11/08 N I want to display (on the first tab) a total of how many times the Name = Smith and the Value = N, which should return a value of 2 from the example above. However, I can't get anything to work. Any help would really be appreciated -- Stormin |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Formula
Thanks for the treminology. As you can see Excel is not my strong point!!
I have tried to use the suggestion below and have entered =SUMPRODUCT((Procedure Tracking!A2:A58="Smith")*(Procedure Tracking!F2:F58="N")) However, I am getting an error message #NAME?. I have checked that the Sheet names are correct, no mispellings etc. Any ideas please? Thanks Rachael -- Stormin "Mike H" wrote: Hi, First a bit of terminology. You have worksheets in a workbook and not tabs in a worksheet. A worksheet has one tab and it displays the name of the worksheet and provide a few bits of functionality. Having got that right try this =SUMPRODUCT((Sheet2!A2:A5="Smith")*(Sheet2!C2:C5=" N")) Mike "Stormin" wrote: Hi I wonder if anyone can help me with the following problem. I have two tabs within a worksheet. The second has a number of colums including those shown below: Name Date Value Smith 11/12 N Jones 11/05 D Smith 11/20 D Smith 11/08 N I want to display (on the first tab) a total of how many times the Name = Smith and the Value = N, which should return a value of 2 from the example above. However, I can't get anything to work. Any help would really be appreciated -- Stormin |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Formula
Make sure the workbook with the sheet you are calculating is open, then
instead of typing in the sheet name and cell range select first the sheet tab and then the range and you will get the correct name. Since there is a space the sheet name it would have to look like =SUMPRODUCT(('Procedure Tracking'!A2:A58="Smith")*('Procedure Tracking'!F2:F58="N")) or =SUMPRODUCT(--('Procedure Tracking'!A2:A58="Smith"),--('Procedure Tracking'!F2:F58="N")) see the apostrophes -- Regards, Peo Sjoblom "Stormin" wrote in message ... Thanks for the treminology. As you can see Excel is not my strong point!! I have tried to use the suggestion below and have entered =SUMPRODUCT((Procedure Tracking!A2:A58="Smith")*(Procedure Tracking!F2:F58="N")) However, I am getting an error message #NAME?. I have checked that the Sheet names are correct, no mispellings etc. Any ideas please? Thanks Rachael -- Stormin "Mike H" wrote: Hi, First a bit of terminology. You have worksheets in a workbook and not tabs in a worksheet. A worksheet has one tab and it displays the name of the worksheet and provide a few bits of functionality. Having got that right try this =SUMPRODUCT((Sheet2!A2:A5="Smith")*(Sheet2!C2:C5=" N")) Mike "Stormin" wrote: Hi I wonder if anyone can help me with the following problem. I have two tabs within a worksheet. The second has a number of colums including those shown below: Name Date Value Smith 11/12 N Jones 11/05 D Smith 11/20 D Smith 11/08 N I want to display (on the first tab) a total of how many times the Name = Smith and the Value = N, which should return a value of 2 from the example above. However, I can't get anything to work. Any help would really be appreciated -- Stormin |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Formula
Peo has told you what the problem is
"Stormin" wrote: Hi Mike Thank you for your response. As you can tell Excel is not my strong point. Unfortunately I am receiving an error of #NAME?. I have checked my spelling, but do you have any ideas please? I typed the formula as below =SUMPRODUCT((Procedure Tracking!A3:A59="EDM")*(Procedure Tracking!F3:F59="D")) Thanks Rachael -- Stormin "Stormin" wrote: Hi I wonder if anyone can help me with the following problem. I have two tabs within a worksheet. The second has a number of colums including those shown below: Name Date Value Smith 11/12 N Jones 11/05 D Smith 11/20 D Smith 11/08 N I want to display (on the first tab) a total of how many times the Name = Smith and the Value = N, which should return a value of 2 from the example above. However, I can't get anything to work. Any help would really be appreciated -- Stormin |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Formula
Thanks both, the second option works, not the first. Mike - hadn't refresh the screen....not used this before!! -- Stormin "Mike H" wrote: Peo has told you what the problem is "Stormin" wrote: Hi Mike Thank you for your response. As you can tell Excel is not my strong point. Unfortunately I am receiving an error of #NAME?. I have checked my spelling, but do you have any ideas please? I typed the formula as below =SUMPRODUCT((Procedure Tracking!A3:A59="EDM")*(Procedure Tracking!F3:F59="D")) Thanks Rachael -- Stormin "Stormin" wrote: Hi I wonder if anyone can help me with the following problem. I have two tabs within a worksheet. The second has a number of colums including those shown below: Name Date Value Smith 11/12 N Jones 11/05 D Smith 11/20 D Smith 11/08 N I want to display (on the first tab) a total of how many times the Name = Smith and the Value = N, which should return a value of 2 from the example above. However, I can't get anything to work. Any help would really be appreciated -- Stormin |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel Formula
Hi Mike
Thank you for your response. As you can tell Excel is not my strong point. Unfortunately I am receiving an error of #NAME?. I have checked my spelling, but do you have any ideas please? I typed the formula as below =SUMPRODUCT((Procedure Tracking!A3:A59="EDM")*(Procedure Tracking!F3:F59="D")) Thanks Rachael -- Stormin "Stormin" wrote: Hi I wonder if anyone can help me with the following problem. I have two tabs within a worksheet. The second has a number of colums including those shown below: Name Date Value Smith 11/12 N Jones 11/05 D Smith 11/20 D Smith 11/08 N I want to display (on the first tab) a total of how many times the Name = Smith and the Value = N, which should return a value of 2 from the example above. However, I can't get anything to work. Any help would really be appreciated -- Stormin |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2002 Formula: Urgent Conditional Formula Required Right Away - if possible | Excel Discussion (Misc queries) | |||
Build excel formula using field values as text in the formula | Excel Worksheet Functions | |||
Excel Formula Doesn't Execute (Shows formula-not the calcuation) | Excel Discussion (Misc queries) | |||
Excel 2002 formula displayed not value formula option not checked | Excel Worksheet Functions | |||
i edit a formula (excel) then it displays formula not answer | Excel Discussion (Misc queries) |