Excel Formula

[Stormin]

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

 

[Mike H]

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]

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

 

[Peo Sjoblom]

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

 

[Mike H]

Peo has told you what the problem is

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]

Thanks both, the second option works, not the first.

Mike - hadn't refresh the screen....not used this before!!

 

[Stormin]

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