All,
I have 2 sheets. Sheet 2 has a list of Software (SW) and Sheet 1 has
multiple fields that include Name (A2 through A50000) and SW (F2 through
F50000). The Names are repeated for all the different SW they have. See
example:
A F
Joe Word
Joe Excel
Bob Word
Sue Word
Sue Excel
#N/A Word
#N/A Excel

What I'd like to do is in Sheet 2 create 2 formulas. 1 that would count the
number of times a certain SW from sheet 2 appears in sheet 1 associated with
a name. 2 would count the number of times a certain SW from sheet 2 appears
in sheet 1 associated with "#NA".

Formula 1 would return a value of 3 for Word and 2 for Excel while formula 2
would return 1 for each. I've tried several methods including Sumif,
IF(Countif) and Vlookup with And and no such luck. Any help you could
provide would be greatly appreciated. Thanks.

Jack

I put "Word" in A2 of Sheet2 and "Excel" in A3 (without the quotes),
and then these formulae in the cells stated:

B2: =SUMPRODUCT((Sheet1!F$2:F$50000=A2)*NOT(ISNA((Shee t1!A$2:A
$50000))))

C2: =SUMPRODUCT((Sheet1!F$2:F$50000=A2)*ISNA((Sheet1!A $2:A$50000)))

then copied B2:C2 into B3:C3 and got this:

Word 3 1
Excel 2 1

Which is what you are after, I think.

Hope this helps.

Pete

On Dec 22, 3:46*pm, Jack.Matos
wrote:
All,
I have 2 sheets. Sheet 2 has a list of Software (SW) and Sheet 1 has
multiple fields that include Name (A2 through A50000) and SW (F2 through
F50000). *The Names are repeated for all the different SW they have. See
example:
A * * * * * *F
Joe * * * *Word
Joe * * * *Excel
Bob * * * Word
Sue * * * Word
Sue * * * Excel
#N/A * * Word
#N/A * * Excel

What I'd like to do is in Sheet 2 create 2 formulas. 1 that would count the
number of times a certain SW from sheet 2 appears in sheet 1 associated with
a name. *2 would count the number of times a certain SW from sheet 2 appears
in sheet 1 associated with "#NA".

Formula 1 would return a value of 3 for Word and 2 for Excel while formula 2
would return 1 for each. *I've tried several methods including Sumif,
IF(Countif) and Vlookup with And and no such luck. *Any help you could
provide would be greatly appreciated. *Thanks.

Jack

Begin by trying this formula somewhere on Sheet1: =COUNTIF(F1:F100,"Word")
If this does not return the value 2, then something is wrong with your data.
For example, do you really have "Word" or is it "Word " with spaces

If the formula works cut and paste it to the other sheet
Now modify it to =COUNTIF(Sheet1!F1:F100,A1) where A1 is the actually the
cell with the entry "Word"

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Jack.Matos" wrote in message
...
All,
I have 2 sheets. Sheet 2 has a list of Software (SW) and Sheet 1 has
multiple fields that include Name (A2 through A50000) and SW (F2 through
F50000). The Names are repeated for all the different SW they have. See
example:
A F
Joe Word
Joe Excel
Bob Word
Sue Word
Sue Excel
#N/A Word
#N/A Excel

What I'd like to do is in Sheet 2 create 2 formulas. 1 that would count
the
number of times a certain SW from sheet 2 appears in sheet 1 associated
with
a name. 2 would count the number of times a certain SW from sheet 2
appears
in sheet 1 associated with "#NA".

Formula 1 would return a value of 3 for Word and 2 for Excel while formula
2
would return 1 for each. I've tried several methods including Sumif,
IF(Countif) and Vlookup with And and no such luck. Any help you could
provide would be greatly appreciated. Thanks.

Jack

Pete,

Thank you for your input. This worked well.

Merry Christmas!

"Pete_UK" wrote:

I put "Word" in A2 of Sheet2 and "Excel" in A3 (without the quotes),
and then these formulae in the cells stated:

B2: =SUMPRODUCT((Sheet1!F$2:F$50000=A2)*NOT(ISNA((Shee t1!A$2:A
$50000))))

C2: =SUMPRODUCT((Sheet1!F$2:F$50000=A2)*ISNA((Sheet1!A $2:A$50000)))

then copied B2:C2 into B3:C3 and got this:

Word 3 1
Excel 2 1

Which is what you are after, I think.

Hope this helps.

Pete

On Dec 22, 3:46 pm, Jack.Matos
wrote:
All,
I have 2 sheets. Sheet 2 has a list of Software (SW) and Sheet 1 has
multiple fields that include Name (A2 through A50000) and SW (F2 through
F50000). The Names are repeated for all the different SW they have. See
example:
A F
Joe Word
Joe Excel
Bob Word
Sue Word
Sue Excel
#N/A Word
#N/A Excel

What I'd like to do is in Sheet 2 create 2 formulas. 1 that would count the
number of times a certain SW from sheet 2 appears in sheet 1 associated with
a name. 2 would count the number of times a certain SW from sheet 2 appears
in sheet 1 associated with "#NA".

Formula 1 would return a value of 3 for Word and 2 for Excel while formula 2
would return 1 for each. I've tried several methods including Sumif,
IF(Countif) and Vlookup with And and no such luck. Any help you could
provide would be greatly appreciated. Thanks.

Jack

Thanks for feeding back, Jack - Merry Christmas to you, too !!

Pete

On Dec 22, 5:02*pm, Jack.Matos
wrote:
Pete,

Thank you for your input. *This worked well. *

Merry Christmas!



"Pete_UK" wrote:
I put "Word" in A2 of Sheet2 and "Excel" in A3 (without the quotes),
and then these formulae in the cells stated:

B2: * =SUMPRODUCT((Sheet1!F$2:F$50000=A2)*NOT(ISNA((Shee t1!A$2:A
$50000))))

C2: * =SUMPRODUCT((Sheet1!F$2:F$50000=A2)*ISNA((Sheet1!A $2:A$50000)))

then copied B2:C2 into B3:C3 and got this:

Word * * 3 * *1
Excel * *2 * *1

Which is what you are after, I think.

Hope this helps.

Pete

On Dec 22, 3:46 pm, Jack.Matos
wrote:
All,
I have 2 sheets. Sheet 2 has a list of Software (SW) and Sheet 1 has
multiple fields that include Name (A2 through A50000) and SW (F2 through
F50000). *The Names are repeated for all the different SW they have.. See
example:
A * * * * * *F
Joe * * * *Word
Joe * * * *Excel
Bob * * * Word
Sue * * * Word
Sue * * * Excel
#N/A * * Word
#N/A * * Excel

What I'd like to do is in Sheet 2 create 2 formulas. 1 that would count the
number of times a certain SW from sheet 2 appears in sheet 1 associated with
a name. *2 would count the number of times a certain SW from sheet 2 appears
in sheet 1 associated with "#NA".

Formula 1 would return a value of 3 for Word and 2 for Excel while formula 2
would return 1 for each. *I've tried several methods including Sumif,
IF(Countif) and Vlookup with And and no such luck. *Any help you could
provide would be greatly appreciated. *Thanks.

Jack- Hide quoted text -

- Show quoted text -