software

I am attempting to create a function to do the following.

I have to columns of data that I need to go through. I am attempting to
talley the number of occurences of each item.

To do this I need to go through column 'a' and every time it is 1, i then
must check column 'b'. If in column b the numbers 1,3,5,6 are found then I
need to add one to the talley. Can anyone suggest a way to do this. I
attempted to use countif formula but could not get something on it to work.
Hi!

Try this:

=SUMPRODUCT(--(A1:A20=1),--(ISNUMBER(MATCH(B1:B20,{1,3,5,6},0))))

Biff

quot;J. McGonigalquot; gt; wrote in message
...
gt;I am attempting to create a function to do the following.
gt;
gt; I have to columns of data that I need to go through. I am attempting to
gt; talley the number of occurences of each item.
gt;
gt; To do this I need to go through column 'a' and every time it is 1, i then
gt; must check column 'b'. If in column b the numbers 1,3,5,6 are found then I
gt; need to add one to the talley. Can anyone suggest a way to do this. I
gt; attempted to use countif formula but could not get something on it to
gt; work.
gt;
gt;
The other option is to create a Pivot Table. Once you understand them, they are
very slick and very powerful. There are several sites, including
www.cpearson.com which explain Pivot Tables.

--
Regards,
Fredquot;J. McGonigalquot; gt; wrote in message
...
gt;I am attempting to create a function to do the following.
gt;
gt; I have to columns of data that I need to go through. I am attempting to
gt; talley the number of occurences of each item.
gt;
gt; To do this I need to go through column 'a' and every time it is 1, i then
gt; must check column 'b'. If in column b the numbers 1,3,5,6 are found then I
gt; need to add one to the talley. Can anyone suggest a way to do this. I
gt; attempted to use countif formula but could not get something on it to work.
gt;
gt;
Thanks, this worked wonderfully! Would mine explaining what the quot;--quot; do?

quot;Biffquot; wrote:

gt; Hi!
gt;
gt; Try this:
gt;
gt; =SUMPRODUCT(--(A1:A20=1),--(ISNUMBER(MATCH(B1:B20,{1,3,5,6},0))))
gt;
gt; Biff
gt;
gt; quot;J. McGonigalquot; gt; wrote in message
gt; ...
gt; gt;I am attempting to create a function to do the following.
gt; gt;
gt; gt; I have to columns of data that I need to go through. I am attempting to
gt; gt; talley the number of occurences of each item.
gt; gt;
gt; gt; To do this I need to go through column 'a' and every time it is 1, i then
gt; gt; must check column 'b'. If in column b the numbers 1,3,5,6 are found then I
gt; gt; need to add one to the talley. Can anyone suggest a way to do this. I
gt; gt; attempted to use countif formula but could not get something on it to
gt; gt; work.
gt; gt;
gt; gt;
gt;
gt;
gt;

Thanks, I will begin to look at this in the near future.

quot;Fred Smithquot; wrote:

gt; The other option is to create a Pivot Table. Once you understand them, they are
gt; very slick and very powerful. There are several sites, including
gt; www.cpearson.com which explain Pivot Tables.
gt;
gt; --
gt; Regards,
gt; Fred
gt;
gt;
gt; quot;J. McGonigalquot; gt; wrote in message
gt; ...
gt; gt;I am attempting to create a function to do the following.
gt; gt;
gt; gt; I have to columns of data that I need to go through. I am attempting to
gt; gt; talley the number of occurences of each item.
gt; gt;
gt; gt; To do this I need to go through column 'a' and every time it is 1, i then
gt; gt; must check column 'b'. If in column b the numbers 1,3,5,6 are found then I
gt; gt; need to add one to the talley. Can anyone suggest a way to do this. I
gt; gt; attempted to use countif formula but could not get something on it to work.
gt; gt;
gt; gt;
gt;
gt;
gt;