Calculations

[J. McGonigal]

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.

[Biff]

Hi!

Try this:

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

Biff

"J. McGonigal" <JMcGonigal@discussions.microsoft.com> wrote in message
news:2CB11996-C62F-4A2B-A628-575A37C63564@microsoft.com...
>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.
>
>

[J. McGonigal]

Thanks, this worked wonderfully! Would mine explaining what the "--" do?

"Biff" wrote:

> Hi!
>
> Try this:
>
> =SUMPRODUCT(--(A1:A20=1),--(ISNUMBER(MATCH(B1:B20,{1,3,5,6},0))))
>
> Biff
>
> "J. McGonigal" <JMcGonigal@discussions.microsoft.com> wrote in message
> news:2CB11996-C62F-4A2B-A628-575A37C63564@microsoft.com...
> >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.
> >
> >
>
>
>

[Fred Smith]

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,
Fred


"J. McGonigal" <JMcGonigal@discussions.microsoft.com> wrote in message
news:2CB11996-C62F-4A2B-A628-575A37C63564@microsoft.com...
>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.
>
>

[J. McGonigal]

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

"Fred Smith" wrote:

> 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,
> Fred
>
>
> "J. McGonigal" <JMcGonigal@discussions.microsoft.com> wrote in message
> news:2CB11996-C62F-4A2B-A628-575A37C63564@microsoft.com...
> >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.
> >
> >
>
>
>