Need Help w/ formulas for multiple criteria
 

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

Need Help w/ formulas for multiple criteria
 

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

Need Help w/ formulas for multiple criteria
 

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

Need Help w/ formulas for multiple criteria
 

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

Need Help w/ formulas for multiple criteria
 

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 -