Function help!

[CaseyDoes]

1. I was trying to use the COUNTIF function on two sheets.

Sheet1
<table border="0" cellspacing="0" cols="2" frame="VOID" rules="NONE"> <colgroup><col width="86"><col width="86"></colgroup> <tbody> <tr> <td style="border: 1px solid rgb(0, 0, 0);" width="86" align="LEFT" height="17">Alpha</td> <td style="border: 1px solid rgb(0, 0, 0);" width="86" align="LEFT">returns the count of "Alpha"(A1), when it is not equal to "c"
</td> </tr> <tr> <td style="border: 1px solid rgb(0, 0, 0);" align="LEFT" height="17">Beta</td> <td style="border: 1px solid rgb(0, 0, 0);" align="LEFT">
</td> </tr> <tr> <td style="border: 1px solid rgb(0, 0, 0);" align="LEFT" height="17">Gamma</td> <td style="border: 1px solid rgb(0, 0, 0);" align="LEFT">
</td> </tr> </tbody> </table>
Sheet 2
<table border="0" cellspacing="0" cols="2" frame="VOID" rules="NONE"> <colgroup><col width="86"><col width="86"></colgroup> <tbody> <tr> <td style="border: 1px solid rgb(0, 0, 0);" width="86" align="LEFT" height="17">Alpha</td> <td style="border: 1px solid rgb(0, 0, 0);" width="86" align="LEFT">c</td> </tr> <tr> <td style="border: 1px solid rgb(0, 0, 0);" align="LEFT" height="17">Alpha</td> <td style="border: 1px solid rgb(0, 0, 0);" align="LEFT">c</td> </tr> <tr> <td style="border: 1px solid rgb(0, 0, 0);" align="LEFT" height="17">Gamma</td> <td style="border: 1px solid rgb(0, 0, 0);" align="LEFT">c</td> </tr> <tr> <td style="border: 1px solid rgb(0, 0, 0);" align="LEFT" height="17">Beta</td> <td style="border: 1px solid rgb(0, 0, 0);" align="LEFT">
</td> </tr> <tr> <td style="border: 1px solid rgb(0, 0, 0);" align="LEFT" height="17">Alpha</td> <td style="border: 1px solid rgb(0, 0, 0);" align="LEFT">
</td> </tr> <tr> <td style="border: 1px solid rgb(0, 0, 0);" align="LEFT" height="17">Gamma</td> <td style="border: 1px solid rgb(0, 0, 0);" align="LEFT">
</td> </tr> <tr> <td style="border: 1px solid rgb(0, 0, 0);" align="LEFT" height="17">Gamma</td> <td style="border: 1px solid rgb(0, 0, 0);" align="LEFT">
</td> </tr> </tbody> </table>
My current attempt was this:
=COUNTIF(Sheet2.$A$1:$A$99;A1 & Sheet2.B1:B7 != 'c')

2.

<table border="0" cellspacing="0" cols="2" frame="VOID" rules="NONE"> <colgroup><col width="86"><col width="86"></colgroup> <tbody> <tr> <td style="border: 1px solid rgb(0, 0, 0);" width="86" align="LEFT" height="17">Alpha</td> <td style="border: 1px solid rgb(0, 0, 0);" width="86" align="LEFT">finds the correlating number of the multiple instances and adds the together.
</td> </tr> <tr> <td style="border: 1px solid rgb(0, 0, 0);" align="LEFT" height="17">Beta</td> <td style="border: 1px solid rgb(0, 0, 0);" align="LEFT">
</td> </tr> <tr> <td style="border: 1px solid rgb(0, 0, 0);" align="LEFT" height="17">Gamma</td> <td style="border: 1px solid rgb(0, 0, 0);" align="LEFT">
</td> </tr> </tbody> </table>

<table border="0" cellspacing="0" cols="2" frame="VOID" rules="NONE"> <colgroup><col width="86"><col width="86"></colgroup> <tbody> <tr> <td style="border: 1px solid rgb(0, 0, 0);" width="86" align="LEFT" height="17">Alpha</td> <td style="border: 1px solid rgb(0, 0, 0);" width="86" align="RIGHT">3</td> </tr> <tr> <td style="border: 1px solid rgb(0, 0, 0);" align="LEFT" height="17">Alpha</td> <td style="border: 1px solid rgb(0, 0, 0);" align="RIGHT">4</td> </tr> <tr> <td style="border: 1px solid rgb(0, 0, 0);" align="LEFT" height="17">Gamma</td> <td style="border: 1px solid rgb(0, 0, 0);" align="RIGHT">6</td> </tr> <tr> <td style="border: 1px solid rgb(0, 0, 0);" align="LEFT" height="17">Beta</td> <td style="border: 1px solid rgb(0, 0, 0);" align="RIGHT">7</td> </tr> <tr> <td style="border: 1px solid rgb(0, 0, 0);" align="LEFT" height="17">Alpha</td> <td style="border: 1px solid rgb(0, 0, 0);" align="RIGHT">2</td> </tr> <tr> <td style="border: 1px solid rgb(0, 0, 0);" align="LEFT" height="17">Gamma</td> <td style="border: 1px solid rgb(0, 0, 0);" align="RIGHT">5</td> </tr> <tr> <td style="border: 1px solid rgb(0, 0, 0);" align="LEFT" height="17">Gamma</td> <td style="border: 1px solid rgb(0, 0, 0);" align="RIGHT">3</td> </tr> </tbody> </table>
In example the function would find A1, A2, & A5 equal to "Alpha" and add 3, 4, 2 together resulting in 9.

Also, what if I wanted to list the results instead of add them? Like My result would be '3,4,2'.

Thanks.

 

[Colin Legg]

The answer to (1) depends on the version of Excel you are using.

If you are using Excel 2007 or later, you can use COUNTIFS() like this:

=COUNTIF(Sheet2!$A$1:$A$99;A1;Sheet2!$B$1:$B$99;"c")

For (2) you can use SUMIF()

=SUMIF(Sheet2!$A$1:$A$99;A1;Sheet2!$B$1:$B$99)

Adjust cell references as necessary.

 

[CaseyDoes]

Sadly I have 2003 up at work.

 

[T. Valko]

Sadly I have 2003 up at work.

=COUNTIF(Sheet2!$A$1:$A$99;A1;Sheet2!$B$1:$B$99;"c")

OK, this will work in any version of Excel:

=SUMPRODUCT(--(Sheet2!$A$1:$A$99=A1);--(Sheet2!$B$1:$B$99="c"))

Use the appropriate argument separator for your location. Semi-colons or commas.

The SUMIF formula will also work in any version of Excel.

 

[CaseyDoes]

SUMPRODUCT & SUMIF worked out great!

Now how would I spit out the list of names?

Also, Since I am new at this is there anywhere to reference all these functions? I just want to print them all out and study study study!

 

[T. Valko]

SUMPRODUCT & SUMIF worked out great!

Now how would I spit out the list of names?

Also, Since I am new at this is there anywhere to reference all these functions? I just want to print them all out and study study study!

If you want the results all in a single cell like 3,4,2 then you'll need to use VBA programming.

If you can use separate cells for each result then it can be done with a formula.

 

[T. Valko]

Also, Since I am new at this is there anywhere to reference all these functions? I just want to print them all out and study study study!

The Excel help files list all the functions with simple examples of their use.