Is there a way to use a formula to determine how many times an alphanumeric
value is used in a column of cells.

For example a column consisting of 300 cells that have one of the folloing
notations in each cell "A1, A2, B1, B2, B3, C1, C2 or C3".

Can a formula be used to determine how many times A2 appears in the column
if so what formula would I use?

Can a formula be used to determine how many times A1 or B3 or C2 appears in
the column if so what formula would I use?

Try this:

=COUNTIF(A1:A300,"A1")

--
Biff
Microsoft Excel MVP


"ksean" wrote in message
...
Is there a way to use a formula to determine how many times an
alphanumeric
value is used in a column of cells.

For example a column consisting of 300 cells that have one of the folloing
notations in each cell "A1, A2, B1, B2, B3, C1, C2 or C3".

Can a formula be used to determine how many times A2 appears in the column
if so what formula would I use?

Can a formula be used to determine how many times A1 or B3 or C2 appears
in
the column if so what formula would I use?

=COUNTIF(A1:A300,"A1")
etc.
--
Gary''s Student - gsnu200907


"ksean" wrote:

Is there a way to use a formula to determine how many times an alphanumeric
value is used in a column of cells.

For example a column consisting of 300 cells that have one of the folloing
notations in each cell "A1, A2, B1, B2, B3, C1, C2 or C3".

Can a formula be used to determine how many times A2 appears in the column
if so what formula would I use?

Can a formula be used to determine how many times A1 or B3 or C2 appears in
the column if so what formula would I use?

Thanks it worked great however now I need to expand the search across 2
columns on a different sheet.

For example:
I need cell B5 on sheet 'stock' to tell me how many times column D on sheet
'Master' displays "blue" and column E displays "frisbee". I am trying to
determine how many times "blue" and "frisbee" appear on the same row side by
side.

Thanks,
Kerry



"T. Valko" wrote:

Try this:

=COUNTIF(A1:A300,"A1")

--
Biff
Microsoft Excel MVP


"ksean" wrote in message
...
Is there a way to use a formula to determine how many times an
alphanumeric
value is used in a column of cells.

For example a column consisting of 300 cells that have one of the folloing
notations in each cell "A1, A2, B1, B2, B3, C1, C2 or C3".

Can a formula be used to determine how many times A2 appears in the column
if so what formula would I use?

Can a formula be used to determine how many times A1 or B3 or C2 appears
in
the column if so what formula would I use?


.

Try this...

=SUMPRODUCT(--(Master!D1:D10="blue"),--(Master!E1:E10="frisbee"))

Better to use cells to hold the criteria:

A1 = blue
B1 = frisbee

=SUMPRODUCT(--(Master!D1:D10=A1),--(Master!E1:E10=B1))

--
Biff
Microsoft Excel MVP


"ksean" wrote in message
...
Thanks it worked great however now I need to expand the search across 2
columns on a different sheet.

For example:
I need cell B5 on sheet 'stock' to tell me how many times column D on
sheet
'Master' displays "blue" and column E displays "frisbee". I am trying to
determine how many times "blue" and "frisbee" appear on the same row side
by
side.

Thanks,
Kerry



"T. Valko" wrote:

Try this:

=COUNTIF(A1:A300,"A1")

--
Biff
Microsoft Excel MVP


"ksean" wrote in message
...
Is there a way to use a formula to determine how many times an
alphanumeric
value is used in a column of cells.

For example a column consisting of 300 cells that have one of the
folloing
notations in each cell "A1, A2, B1, B2, B3, C1, C2 or C3".

Can a formula be used to determine how many times A2 appears in the
column
if so what formula would I use?

Can a formula be used to determine how many times A1 or B3 or C2
appears
in
the column if so what formula would I use?


.

I can't get your formula to work as you have shown it, in your formula what
does the -- stand for? I am still missing something!



"T. Valko" wrote:

Try this...

=SUMPRODUCT(--(Master!D1:D10="blue"),--(Master!E1:E10="frisbee"))

Better to use cells to hold the criteria:

A1 = blue
B1 = frisbee

=SUMPRODUCT(--(Master!D1:D10=A1),--(Master!E1:E10=B1))

--
Biff
Microsoft Excel MVP


"ksean" wrote in message
...
Thanks it worked great however now I need to expand the search across 2
columns on a different sheet.

For example:
I need cell B5 on sheet 'stock' to tell me how many times column D on
sheet
'Master' displays "blue" and column E displays "frisbee". I am trying to
determine how many times "blue" and "frisbee" appear on the same row side
by
side.

Thanks,
Kerry



"T. Valko" wrote:

Try this:

=COUNTIF(A1:A300,"A1")

--
Biff
Microsoft Excel MVP


"ksean" wrote in message
...
Is there a way to use a formula to determine how many times an
alphanumeric
value is used in a column of cells.

For example a column consisting of 300 cells that have one of the
folloing
notations in each cell "A1, A2, B1, B2, B3, C1, C2 or C3".

Can a formula be used to determine how many times A2 appears in the
column
if so what formula would I use?

Can a formula be used to determine how many times A1 or B3 or C2
appears
in
the column if so what formula would I use?


.



.

Thanks it worked great however now I need to expand the search across 2
columns on a different sheet.

For example:
I need cell B5 on sheet 'stock' to tell me how many times column D on sheet
'Master' displays "blue" and column E displays "frisbee". I am trying to
determine how many times "blue" and "frisbee" appear on the same row side by
side.

Thanks,
Kerry




"Gary''s Student" wrote:

=COUNTIF(A1:A300,"A1")
etc.
--
Gary''s Student - gsnu200907


"ksean" wrote:

Is there a way to use a formula to determine how many times an alphanumeric
value is used in a column of cells.

For example a column consisting of 300 cells that have one of the folloing
notations in each cell "A1, A2, B1, B2, B3, C1, C2 or C3".

Can a formula be used to determine how many times A2 appears in the column
if so what formula would I use?

Can a formula be used to determine how many times A1 or B3 or C2 appears in
the column if so what formula would I use?

I can't get your formula to work

Kind of vague. What exactly does that mean? You get an error? You get the
wrong result? You don't get any result? Your hard drive crashed?

Post the *exact* formula you tried.

what does the -- stand for?

See this:

http://xldynamic.com/source/xld.SUMPRODUCT.html

--
Biff
Microsoft Excel MVP


"ksean" wrote in message
...
I can't get your formula to work as you have shown it, in your formula what
does the -- stand for? I am still missing something!



"T. Valko" wrote:

Try this...

=SUMPRODUCT(--(Master!D1:D10="blue"),--(Master!E1:E10="frisbee"))

Better to use cells to hold the criteria:

A1 = blue
B1 = frisbee

=SUMPRODUCT(--(Master!D1:D10=A1),--(Master!E1:E10=B1))

--
Biff
Microsoft Excel MVP


"ksean" wrote in message
...
Thanks it worked great however now I need to expand the search across 2
columns on a different sheet.

For example:
I need cell B5 on sheet 'stock' to tell me how many times column D on
sheet
'Master' displays "blue" and column E displays "frisbee". I am trying
to
determine how many times "blue" and "frisbee" appear on the same row
side
by
side.

Thanks,
Kerry



"T. Valko" wrote:

Try this:

=COUNTIF(A1:A300,"A1")

--
Biff
Microsoft Excel MVP


"ksean" wrote in message
...
Is there a way to use a formula to determine how many times an
alphanumeric
value is used in a column of cells.

For example a column consisting of 300 cells that have one of the
folloing
notations in each cell "A1, A2, B1, B2, B3, C1, C2 or C3".

Can a formula be used to determine how many times A2 appears in the
column
if so what formula would I use?

Can a formula be used to determine how many times A1 or B3 or C2
appears
in
the column if so what formula would I use?


.



.

I used your exact formula and it didn't work then I assumed the -- meant
needed to add in where the -- was

1st =SUMPRODUCT(--(Master!D1:D10="blue"),--(Master!E1:E10="frisbee"))
2nd
=SUMPRODUCT((countif(Master!D1:D10="blue")),(count if(Master!E1:E10="frisbee")))

Kerry


"T. Valko" wrote:

I can't get your formula to work

Kind of vague. What exactly does that mean? You get an error? You get the
wrong result? You don't get any result? Your hard drive crashed?

Post the *exact* formula you tried.

what does the -- stand for?

See this:

http://xldynamic.com/source/xld.SUMPRODUCT.html

--
Biff
Microsoft Excel MVP


"ksean" wrote in message
...
I can't get your formula to work as you have shown it, in your formula what
does the -- stand for? I am still missing something!



"T. Valko" wrote:

Try this...

=SUMPRODUCT(--(Master!D1:D10="blue"),--(Master!E1:E10="frisbee"))

Better to use cells to hold the criteria:

A1 = blue
B1 = frisbee

=SUMPRODUCT(--(Master!D1:D10=A1),--(Master!E1:E10=B1))

--
Biff
Microsoft Excel MVP


"ksean" wrote in message
...
Thanks it worked great however now I need to expand the search across 2
columns on a different sheet.

For example:
I need cell B5 on sheet 'stock' to tell me how many times column D on
sheet
'Master' displays "blue" and column E displays "frisbee". I am trying
to
determine how many times "blue" and "frisbee" appear on the same row
side
by
side.

Thanks,
Kerry



"T. Valko" wrote:

Try this:

=COUNTIF(A1:A300,"A1")

--
Biff
Microsoft Excel MVP


"ksean" wrote in message
...
Is there a way to use a formula to determine how many times an
alphanumeric
value is used in a column of cells.

For example a column consisting of 300 cells that have one of the
folloing
notations in each cell "A1, A2, B1, B2, B3, C1, C2 or C3".

Can a formula be used to determine how many times A2 appears in the
column
if so what formula would I use?

Can a formula be used to determine how many times A1 or B3 or C2
appears
in
the column if so what formula would I use?


.



.



.

I used your exact formula and it didn't work

For me to try to offer suggestions you have to help me understand what "it
didn't work" means.

Consider this: I tell you my car won't start. So, what do you think I should
do about it?

You could go through a long list of possible causes but what you'd really
want is MORE info from me that would help you narrow down the possible
causes.

So, what you're telling me is that your car won't start!

--
Biff
Microsoft Excel MVP


"ksean" wrote in message
...
I used your exact formula and it didn't work then I assumed the -- meant
needed to add in where the -- was

1st =SUMPRODUCT(--(Master!D1:D10="blue"),--(Master!E1:E10="frisbee"))
2nd
=SUMPRODUCT((countif(Master!D1:D10="blue")),(count if(Master!E1:E10="frisbee")))

Kerry


"T. Valko" wrote:

I can't get your formula to work

Kind of vague. What exactly does that mean? You get an error? You get the
wrong result? You don't get any result? Your hard drive crashed?

Post the *exact* formula you tried.

what does the -- stand for?

See this:

http://xldynamic.com/source/xld.SUMPRODUCT.html

--
Biff
Microsoft Excel MVP


"ksean" wrote in message
...
I can't get your formula to work as you have shown it, in your formula
what
does the -- stand for? I am still missing something!



"T. Valko" wrote:

Try this...

=SUMPRODUCT(--(Master!D1:D10="blue"),--(Master!E1:E10="frisbee"))

Better to use cells to hold the criteria:

A1 = blue
B1 = frisbee

=SUMPRODUCT(--(Master!D1:D10=A1),--(Master!E1:E10=B1))

--
Biff
Microsoft Excel MVP


"ksean" wrote in message
...
Thanks it worked great however now I need to expand the search
across 2
columns on a different sheet.

For example:
I need cell B5 on sheet 'stock' to tell me how many times column D
on
sheet
'Master' displays "blue" and column E displays "frisbee". I am
trying
to
determine how many times "blue" and "frisbee" appear on the same row
side
by
side.

Thanks,
Kerry



"T. Valko" wrote:

Try this:

=COUNTIF(A1:A300,"A1")

--
Biff
Microsoft Excel MVP


"ksean" wrote in message
...
Is there a way to use a formula to determine how many times an
alphanumeric
value is used in a column of cells.

For example a column consisting of 300 cells that have one of the
folloing
notations in each cell "A1, A2, B1, B2, B3, C1, C2 or C3".

Can a formula be used to determine how many times A2 appears in
the
column
if so what formula would I use?

Can a formula be used to determine how many times A1 or B3 or C2
appears
in
the column if so what formula would I use?


.



.



.

Hello T. Vallko, Thanks for all your help so far, the link you provided was
very helpful.

I was able to get this formula to work

=SUMPRODUCT((Master!E3:E800="Blue")*(Master!I3:I80 0="frisbee"))

But now I have run into another problem (will it never end??).

When "frisbee" is the result of another formula the SUMPRODUCT formula
doesn't work. How can I get the SUMPRODUCT formula to work based on the
value of "frisbee" when frisbee is the result of a formula?

Thanks


"ksean" wrote:

I used your exact formula and it didn't work then I assumed the -- meant
needed to add in where the -- was

1st =SUMPRODUCT(--(Master!D1:D10="blue"),--(Master!E1:E10="frisbee"))
2nd
=SUMPRODUCT((countif(Master!D1:D10="blue")),(count if(Master!E1:E10="frisbee")))

Kerry


"T. Valko" wrote:

I can't get your formula to work

Kind of vague. What exactly does that mean? You get an error? You get the
wrong result? You don't get any result? Your hard drive crashed?

Post the *exact* formula you tried.

what does the -- stand for?

See this:

http://xldynamic.com/source/xld.SUMPRODUCT.html

--
Biff
Microsoft Excel MVP


"ksean" wrote in message
...
I can't get your formula to work as you have shown it, in your formula what
does the -- stand for? I am still missing something!



"T. Valko" wrote:

Try this...

=SUMPRODUCT(--(Master!D1:D10="blue"),--(Master!E1:E10="frisbee"))

Better to use cells to hold the criteria:

A1 = blue
B1 = frisbee

=SUMPRODUCT(--(Master!D1:D10=A1),--(Master!E1:E10=B1))

--
Biff
Microsoft Excel MVP


"ksean" wrote in message
...
Thanks it worked great however now I need to expand the search across 2
columns on a different sheet.

For example:
I need cell B5 on sheet 'stock' to tell me how many times column D on
sheet
'Master' displays "blue" and column E displays "frisbee". I am trying
to
determine how many times "blue" and "frisbee" appear on the same row
side
by
side.

Thanks,
Kerry



"T. Valko" wrote:

Try this:

=COUNTIF(A1:A300,"A1")

--
Biff
Microsoft Excel MVP


"ksean" wrote in message
...
Is there a way to use a formula to determine how many times an
alphanumeric
value is used in a column of cells.

For example a column consisting of 300 cells that have one of the
folloing
notations in each cell "A1, A2, B1, B2, B3, C1, C2 or C3".

Can a formula be used to determine how many times A2 appears in the
column
if so what formula would I use?

Can a formula be used to determine how many times A1 or B3 or C2
appears
in
the column if so what formula would I use?


.



.



.

That (based on another formula) doesn't matter.

Maybe you have a typo (frisbee may have and extra space character before or
after???).

ksean wrote:

Hello T. Vallko, Thanks for all your help so far, the link you provided was
very helpful.

I was able to get this formula to work

=SUMPRODUCT((Master!E3:E800="Blue")*(Master!I3:I80 0="frisbee"))

But now I have run into another problem (will it never end??).

When "frisbee" is the result of another formula the SUMPRODUCT formula
doesn't work. How can I get the SUMPRODUCT formula to work based on the
value of "frisbee" when frisbee is the result of a formula?

Thanks

"ksean" wrote:

I used your exact formula and it didn't work then I assumed the -- meant
needed to add in where the -- was

1st =SUMPRODUCT(--(Master!D1:D10="blue"),--(Master!E1:E10="frisbee"))
2nd
=SUMPRODUCT((countif(Master!D1:D10="blue")),(count if(Master!E1:E10="frisbee")))

Kerry


"T. Valko" wrote:

I can't get your formula to work

Kind of vague. What exactly does that mean? You get an error? You get the
wrong result? You don't get any result? Your hard drive crashed?

Post the *exact* formula you tried.

what does the -- stand for?

See this:

http://xldynamic.com/source/xld.SUMPRODUCT.html

--
Biff
Microsoft Excel MVP


"ksean" wrote in message
...
I can't get your formula to work as you have shown it, in your formula what
does the -- stand for? I am still missing something!



"T. Valko" wrote:

Try this...

=SUMPRODUCT(--(Master!D1:D10="blue"),--(Master!E1:E10="frisbee"))

Better to use cells to hold the criteria:

A1 = blue
B1 = frisbee

=SUMPRODUCT(--(Master!D1:D10=A1),--(Master!E1:E10=B1))

--
Biff
Microsoft Excel MVP


"ksean" wrote in message
...
Thanks it worked great however now I need to expand the search across 2
columns on a different sheet.

For example:
I need cell B5 on sheet 'stock' to tell me how many times column D on
sheet
'Master' displays "blue" and column E displays "frisbee". I am trying
to
determine how many times "blue" and "frisbee" appear on the same row
side
by
side.

Thanks,
Kerry



"T. Valko" wrote:

Try this:

=COUNTIF(A1:A300,"A1")

--
Biff
Microsoft Excel MVP


"ksean" wrote in message
...
Is there a way to use a formula to determine how many times an
alphanumeric
value is used in a column of cells.

For example a column consisting of 300 cells that have one of the
folloing
notations in each cell "A1, A2, B1, B2, B3, C1, C2 or C3".

Can a formula be used to determine how many times A2 appears in the
column
if so what formula would I use?

Can a formula be used to determine how many times A1 or B3 or C2
appears
in
the column if so what formula would I use?


.



.



.


--

Dave Peterson

What does the formula that returns frisbee look like?

If the SUMPRODUCT isn't counting "frisbee" then that sounds like the other
formula returns something along with frisbee, like maybe some kind of unseen
whitespace character.

Try one of these:

=SUMPRODUCT(--(Master!E3:E800="Blue"),--(TRIM(Master!I3:I800)="frisbee"))

=SUMPRODUCT(--(Master!E3:E800="Blue"),--(ISNUMBER(SEARCH("frisbee",Master!I3:I800))))

--
Biff
Microsoft Excel MVP


"ksean" wrote in message
...
Hello T. Vallko, Thanks for all your help so far, the link you provided
was
very helpful.

I was able to get this formula to work

=SUMPRODUCT((Master!E3:E800="Blue")*(Master!I3:I80 0="frisbee"))

But now I have run into another problem (will it never end??).

When "frisbee" is the result of another formula the SUMPRODUCT formula
doesn't work. How can I get the SUMPRODUCT formula to work based on the
value of "frisbee" when frisbee is the result of a formula?

Thanks


"ksean" wrote:

I used your exact formula and it didn't work then I assumed the -- meant
needed to add in where the -- was

1st =SUMPRODUCT(--(Master!D1:D10="blue"),--(Master!E1:E10="frisbee"))
2nd
=SUMPRODUCT((countif(Master!D1:D10="blue")),(count if(Master!E1:E10="frisbee")))

Kerry


"T. Valko" wrote:

I can't get your formula to work

Kind of vague. What exactly does that mean? You get an error? You get
the
wrong result? You don't get any result? Your hard drive crashed?

Post the *exact* formula you tried.

what does the -- stand for?

See this:

http://xldynamic.com/source/xld.SUMPRODUCT.html

--
Biff
Microsoft Excel MVP


"ksean" wrote in message
...
I can't get your formula to work as you have shown it, in your formula
what
does the -- stand for? I am still missing something!



"T. Valko" wrote:

Try this...

=SUMPRODUCT(--(Master!D1:D10="blue"),--(Master!E1:E10="frisbee"))

Better to use cells to hold the criteria:

A1 = blue
B1 = frisbee

=SUMPRODUCT(--(Master!D1:D10=A1),--(Master!E1:E10=B1))

--
Biff
Microsoft Excel MVP


"ksean" wrote in message
...
Thanks it worked great however now I need to expand the search
across 2
columns on a different sheet.

For example:
I need cell B5 on sheet 'stock' to tell me how many times column D
on
sheet
'Master' displays "blue" and column E displays "frisbee". I am
trying
to
determine how many times "blue" and "frisbee" appear on the same
row
side
by
side.

Thanks,
Kerry



"T. Valko" wrote:

Try this:

=COUNTIF(A1:A300,"A1")

--
Biff
Microsoft Excel MVP


"ksean" wrote in message
...
Is there a way to use a formula to determine how many times an
alphanumeric
value is used in a column of cells.

For example a column consisting of 300 cells that have one of
the
folloing
notations in each cell "A1, A2, B1, B2, B3, C1, C2 or C3".

Can a formula be used to determine how many times A2 appears in
the
column
if so what formula would I use?

Can a formula be used to determine how many times A1 or B3 or
C2
appears
in
the column if so what formula would I use?


.



.



.

Ok... I figured out what the problem is.

Both you and Dave Peterson are correct the formula in the frisbee column is
not the problem.

The problem is that there are several cells in the frisbee column where the
formula in the cell has returned a #N/A . The formula that we have been
working to create does not like this #N/A.

Do you have any ideas on how to work around the #N/A ?



"T. Valko" wrote:

What does the formula that returns frisbee look like?

If the SUMPRODUCT isn't counting "frisbee" then that sounds like the other
formula returns something along with frisbee, like maybe some kind of unseen
whitespace character.

Try one of these:

=SUMPRODUCT(--(Master!E3:E800="Blue"),--(TRIM(Master!I3:I800)="frisbee"))

=SUMPRODUCT(--(Master!E3:E800="Blue"),--(ISNUMBER(SEARCH("frisbee",Master!I3:I800))))

--
Biff
Microsoft Excel MVP


"ksean" wrote in message
...
Hello T. Vallko, Thanks for all your help so far, the link you provided
was
very helpful.

I was able to get this formula to work

=SUMPRODUCT((Master!E3:E800="Blue")*(Master!I3:I80 0="frisbee"))

But now I have run into another problem (will it never end??).

When "frisbee" is the result of another formula the SUMPRODUCT formula
doesn't work. How can I get the SUMPRODUCT formula to work based on the
value of "frisbee" when frisbee is the result of a formula?

Thanks


"ksean" wrote:

I used your exact formula and it didn't work then I assumed the -- meant
needed to add in where the -- was

1st =SUMPRODUCT(--(Master!D1:D10="blue"),--(Master!E1:E10="frisbee"))
2nd
=SUMPRODUCT((countif(Master!D1:D10="blue")),(count if(Master!E1:E10="frisbee")))

Kerry


"T. Valko" wrote:

I can't get your formula to work

Kind of vague. What exactly does that mean? You get an error? You get
the
wrong result? You don't get any result? Your hard drive crashed?

Post the *exact* formula you tried.

what does the -- stand for?

See this:

http://xldynamic.com/source/xld.SUMPRODUCT.html

--
Biff
Microsoft Excel MVP


"ksean" wrote in message
...
I can't get your formula to work as you have shown it, in your formula
what
does the -- stand for? I am still missing something!



"T. Valko" wrote:

Try this...

=SUMPRODUCT(--(Master!D1:D10="blue"),--(Master!E1:E10="frisbee"))

Better to use cells to hold the criteria:

A1 = blue
B1 = frisbee

=SUMPRODUCT(--(Master!D1:D10=A1),--(Master!E1:E10=B1))

--
Biff
Microsoft Excel MVP


"ksean" wrote in message
...
Thanks it worked great however now I need to expand the search
across 2
columns on a different sheet.

For example:
I need cell B5 on sheet 'stock' to tell me how many times column D
on
sheet
'Master' displays "blue" and column E displays "frisbee". I am
trying
to
determine how many times "blue" and "frisbee" appear on the same
row
side
by
side.

Thanks,
Kerry



"T. Valko" wrote:

Try this:

=COUNTIF(A1:A300,"A1")

--
Biff
Microsoft Excel MVP


"ksean" wrote in message
...
Is there a way to use a formula to determine how many times an
alphanumeric
value is used in a column of cells.

For example a column consisting of 300 cells that have one of
the
folloing
notations in each cell "A1, A2, B1, B2, B3, C1, C2 or C3".

Can a formula be used to determine how many times A2 appears in
the
column
if so what formula would I use?

Can a formula be used to determine how many times A1 or B3 or
C2
appears
in
the column if so what formula would I use?


.



.



.



.

Do you have any ideas on how to work around the #N/A ?

=SUMPRODUCT(--(Master!E3:E800="Blue"),--(ISNUMBER(SEARCH("frisbee",Master!I3:I800))))

--
Biff
Microsoft Excel MVP


"ksean" wrote in message
...
Ok... I figured out what the problem is.

Both you and Dave Peterson are correct the formula in the frisbee column
is
not the problem.

The problem is that there are several cells in the frisbee column where
the
formula in the cell has returned a #N/A . The formula that we have been
working to create does not like this #N/A.

Do you have any ideas on how to work around the #N/A ?



"T. Valko" wrote:

What does the formula that returns frisbee look like?

If the SUMPRODUCT isn't counting "frisbee" then that sounds like the
other
formula returns something along with frisbee, like maybe some kind of
unseen
whitespace character.

Try one of these:

=SUMPRODUCT(--(Master!E3:E800="Blue"),--(TRIM(Master!I3:I800)="frisbee"))

=SUMPRODUCT(--(Master!E3:E800="Blue"),--(ISNUMBER(SEARCH("frisbee",Master!I3:I800))))

--
Biff
Microsoft Excel MVP


"ksean" wrote in message
...
Hello T. Vallko, Thanks for all your help so far, the link you provided
was
very helpful.

I was able to get this formula to work

=SUMPRODUCT((Master!E3:E800="Blue")*(Master!I3:I80 0="frisbee"))

But now I have run into another problem (will it never end??).

When "frisbee" is the result of another formula the SUMPRODUCT formula
doesn't work. How can I get the SUMPRODUCT formula to work based on
the
value of "frisbee" when frisbee is the result of a formula?

Thanks


"ksean" wrote:

I used your exact formula and it didn't work then I assumed the --
meant
needed to add in where the -- was

1st =SUMPRODUCT(--(Master!D1:D10="blue"),--(Master!E1:E10="frisbee"))
2nd
=SUMPRODUCT((countif(Master!D1:D10="blue")),(count if(Master!E1:E10="frisbee")))

Kerry


"T. Valko" wrote:

I can't get your formula to work

Kind of vague. What exactly does that mean? You get an error? You
get
the
wrong result? You don't get any result? Your hard drive crashed?

Post the *exact* formula you tried.

what does the -- stand for?

See this:

http://xldynamic.com/source/xld.SUMPRODUCT.html

--
Biff
Microsoft Excel MVP


"ksean" wrote in message
...
I can't get your formula to work as you have shown it, in your
formula
what
does the -- stand for? I am still missing something!



"T. Valko" wrote:

Try this...

=SUMPRODUCT(--(Master!D1:D10="blue"),--(Master!E1:E10="frisbee"))

Better to use cells to hold the criteria:

A1 = blue
B1 = frisbee

=SUMPRODUCT(--(Master!D1:D10=A1),--(Master!E1:E10=B1))

--
Biff
Microsoft Excel MVP


"ksean" wrote in message
...
Thanks it worked great however now I need to expand the search
across 2
columns on a different sheet.

For example:
I need cell B5 on sheet 'stock' to tell me how many times
column D
on
sheet
'Master' displays "blue" and column E displays "frisbee". I am
trying
to
determine how many times "blue" and "frisbee" appear on the
same
row
side
by
side.

Thanks,
Kerry



"T. Valko" wrote:

Try this:

=COUNTIF(A1:A300,"A1")

--
Biff
Microsoft Excel MVP


"ksean" wrote in message
...
Is there a way to use a formula to determine how many times
an
alphanumeric
value is used in a column of cells.

For example a column consisting of 300 cells that have one
of
the
folloing
notations in each cell "A1, A2, B1, B2, B3, C1, C2 or C3".

Can a formula be used to determine how many times A2 appears
in
the
column
if so what formula would I use?

Can a formula be used to determine how many times A1 or B3
or
C2
appears
in
the column if so what formula would I use?


.



.



.



.

Another way is to use an array entered formula.

=SUM((Master!E3:E800="Blue")
*IF(ISNA(Master!I3:I800),0,(Master!I3:I800="Frisbe e")))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

The difference between this suggestion and Biff's is how something like:

xxxxFrisbeeyyyy
is treated in the count.

This =sum() array formula will ignore this entry.

Biff's =isnumber(search(...)) version will include it.





ksean wrote:

Ok... I figured out what the problem is.

Both you and Dave Peterson are correct the formula in the frisbee column is
not the problem.

The problem is that there are several cells in the frisbee column where the
formula in the cell has returned a #N/A . The formula that we have been
working to create does not like this #N/A.

Do you have any ideas on how to work around the #N/A ?

"T. Valko" wrote:

What does the formula that returns frisbee look like?

If the SUMPRODUCT isn't counting "frisbee" then that sounds like the other
formula returns something along with frisbee, like maybe some kind of unseen
whitespace character.

Try one of these:

=SUMPRODUCT(--(Master!E3:E800="Blue"),--(TRIM(Master!I3:I800)="frisbee"))

=SUMPRODUCT(--(Master!E3:E800="Blue"),--(ISNUMBER(SEARCH("frisbee",Master!I3:I800))))

--
Biff
Microsoft Excel MVP


"ksean" wrote in message
...
Hello T. Vallko, Thanks for all your help so far, the link you provided
was
very helpful.

I was able to get this formula to work

=SUMPRODUCT((Master!E3:E800="Blue")*(Master!I3:I80 0="frisbee"))

But now I have run into another problem (will it never end??).

When "frisbee" is the result of another formula the SUMPRODUCT formula
doesn't work. How can I get the SUMPRODUCT formula to work based on the
value of "frisbee" when frisbee is the result of a formula?

Thanks


"ksean" wrote:

I used your exact formula and it didn't work then I assumed the -- meant
needed to add in where the -- was

1st =SUMPRODUCT(--(Master!D1:D10="blue"),--(Master!E1:E10="frisbee"))
2nd
=SUMPRODUCT((countif(Master!D1:D10="blue")),(count if(Master!E1:E10="frisbee")))

Kerry


"T. Valko" wrote:

I can't get your formula to work

Kind of vague. What exactly does that mean? You get an error? You get
the
wrong result? You don't get any result? Your hard drive crashed?

Post the *exact* formula you tried.

what does the -- stand for?

See this:

http://xldynamic.com/source/xld.SUMPRODUCT.html

--
Biff
Microsoft Excel MVP


"ksean" wrote in message
...
I can't get your formula to work as you have shown it, in your formula
what
does the -- stand for? I am still missing something!



"T. Valko" wrote:

Try this...

=SUMPRODUCT(--(Master!D1:D10="blue"),--(Master!E1:E10="frisbee"))

Better to use cells to hold the criteria:

A1 = blue
B1 = frisbee

=SUMPRODUCT(--(Master!D1:D10=A1),--(Master!E1:E10=B1))

--
Biff
Microsoft Excel MVP


"ksean" wrote in message
...
Thanks it worked great however now I need to expand the search
across 2
columns on a different sheet.

For example:
I need cell B5 on sheet 'stock' to tell me how many times column D
on
sheet
'Master' displays "blue" and column E displays "frisbee". I am
trying
to
determine how many times "blue" and "frisbee" appear on the same
row
side
by
side.

Thanks,
Kerry



"T. Valko" wrote:

Try this:

=COUNTIF(A1:A300,"A1")

--
Biff
Microsoft Excel MVP


"ksean" wrote in message
...
Is there a way to use a formula to determine how many times an
alphanumeric
value is used in a column of cells.

For example a column consisting of 300 cells that have one of
the
folloing
notations in each cell "A1, A2, B1, B2, B3, C1, C2 or C3".

Can a formula be used to determine how many times A2 appears in
the
column
if so what formula would I use?

Can a formula be used to determine how many times A1 or B3 or
C2
appears
in
the column if so what formula would I use?


.



.



.



.


--

Dave Peterson

Biff and Dave,

Both your formulas worked great providing the #N/A is only in one column but
when I tried to adapt the formula for use in another spreadsheet it returned
a #N/A value. I discovered that in the second spreadsheet there are rows
where both columns E & I have #N/A in them.

Any thoughts on how to get around this?

What is the possibility of adding a third and forth search criteria?

Kerry



"Dave Peterson" wrote:

Another way is to use an array entered formula.

=SUM((Master!E3:E800="Blue")
*IF(ISNA(Master!I3:I800),0,(Master!I3:I800="Frisbe e")))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

The difference between this suggestion and Biff's is how something like:

xxxxFrisbeeyyyy
is treated in the count.

This =sum() array formula will ignore this entry.

Biff's =isnumber(search(...)) version will include it.





ksean wrote:

Ok... I figured out what the problem is.

Both you and Dave Peterson are correct the formula in the frisbee column is
not the problem.

The problem is that there are several cells in the frisbee column where the
formula in the cell has returned a #N/A . The formula that we have been
working to create does not like this #N/A.

Do you have any ideas on how to work around the #N/A ?

"T. Valko" wrote:

What does the formula that returns frisbee look like?

If the SUMPRODUCT isn't counting "frisbee" then that sounds like the other
formula returns something along with frisbee, like maybe some kind of unseen
whitespace character.

Try one of these:

=SUMPRODUCT(--(Master!E3:E800="Blue"),--(TRIM(Master!I3:I800)="frisbee"))

=SUMPRODUCT(--(Master!E3:E800="Blue"),--(ISNUMBER(SEARCH("frisbee",Master!I3:I800))))

--
Biff
Microsoft Excel MVP


"ksean" wrote in message
...
Hello T. Vallko, Thanks for all your help so far, the link you provided
was
very helpful.

I was able to get this formula to work

=SUMPRODUCT((Master!E3:E800="Blue")*(Master!I3:I80 0="frisbee"))

But now I have run into another problem (will it never end??).

When "frisbee" is the result of another formula the SUMPRODUCT formula
doesn't work. How can I get the SUMPRODUCT formula to work based on the
value of "frisbee" when frisbee is the result of a formula?

Thanks


"ksean" wrote:

I used your exact formula and it didn't work then I assumed the -- meant
needed to add in where the -- was

1st =SUMPRODUCT(--(Master!D1:D10="blue"),--(Master!E1:E10="frisbee"))
2nd
=SUMPRODUCT((countif(Master!D1:D10="blue")),(count if(Master!E1:E10="frisbee")))

Kerry


"T. Valko" wrote:

I can't get your formula to work

Kind of vague. What exactly does that mean? You get an error? You get
the
wrong result? You don't get any result? Your hard drive crashed?

Post the *exact* formula you tried.

what does the -- stand for?

See this:

http://xldynamic.com/source/xld.SUMPRODUCT.html

--
Biff
Microsoft Excel MVP


"ksean" wrote in message
...
I can't get your formula to work as you have shown it, in your formula
what
does the -- stand for? I am still missing something!



"T. Valko" wrote:

Try this...

=SUMPRODUCT(--(Master!D1:D10="blue"),--(Master!E1:E10="frisbee"))

Better to use cells to hold the criteria:

A1 = blue
B1 = frisbee

=SUMPRODUCT(--(Master!D1:D10=A1),--(Master!E1:E10=B1))

--
Biff
Microsoft Excel MVP


"ksean" wrote in message
...
Thanks it worked great however now I need to expand the search
across 2
columns on a different sheet.

For example:
I need cell B5 on sheet 'stock' to tell me how many times column D
on
sheet
'Master' displays "blue" and column E displays "frisbee". I am
trying
to
determine how many times "blue" and "frisbee" appear on the same
row
side
by
side.

Thanks,
Kerry



"T. Valko" wrote:

Try this:

=COUNTIF(A1:A300,"A1")

--
Biff
Microsoft Excel MVP


"ksean" wrote in message
...
Is there a way to use a formula to determine how many times an
alphanumeric
value is used in a column of cells.

For example a column consisting of 300 cells that have one of
the
folloing
notations in each cell "A1, A2, B1, B2, B3, C1, C2 or C3".

Can a formula be used to determine how many times A2 appears in
the
column
if so what formula would I use?

Can a formula be used to determine how many times A1 or B3 or
C2
appears
in
the column if so what formula would I use?


.



.



.



.


--

Dave Peterson
.

=SUM(if(isna(Master!E3:E800),0,(master!e3:e800="Bl ue"))
*IF(ISNA(Master!I3:I800),0,(Master!I3:I800="Frisbe e")))

Still array entered.

If you look close enough, you'll see that there's an if/then/else patthern in
each portion of the formula. Use the if() to look for an error. If it's an
error, use 0. Else use the array of true/falses created by:
(master!e3:e800="Blue").

You'll just keep adding to that pattern.

ksean wrote:

Biff and Dave,

Both your formulas worked great providing the #N/A is only in one column but
when I tried to adapt the formula for use in another spreadsheet it returned
a #N/A value. I discovered that in the second spreadsheet there are rows
where both columns E & I have #N/A in them.

Any thoughts on how to get around this?

What is the possibility of adding a third and forth search criteria?

Kerry

"Dave Peterson" wrote:

Another way is to use an array entered formula.

=SUM((Master!E3:E800="Blue")
*IF(ISNA(Master!I3:I800),0,(Master!I3:I800="Frisbe e")))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

The difference between this suggestion and Biff's is how something like:

xxxxFrisbeeyyyy
is treated in the count.

This =sum() array formula will ignore this entry.

Biff's =isnumber(search(...)) version will include it.





ksean wrote:

Ok... I figured out what the problem is.

Both you and Dave Peterson are correct the formula in the frisbee column is
not the problem.

The problem is that there are several cells in the frisbee column where the
formula in the cell has returned a #N/A . The formula that we have been
working to create does not like this #N/A.

Do you have any ideas on how to work around the #N/A ?

"T. Valko" wrote:

What does the formula that returns frisbee look like?

If the SUMPRODUCT isn't counting "frisbee" then that sounds like the other
formula returns something along with frisbee, like maybe some kind of unseen
whitespace character.

Try one of these:

=SUMPRODUCT(--(Master!E3:E800="Blue"),--(TRIM(Master!I3:I800)="frisbee"))

=SUMPRODUCT(--(Master!E3:E800="Blue"),--(ISNUMBER(SEARCH("frisbee",Master!I3:I800))))

--
Biff
Microsoft Excel MVP


"ksean" wrote in message
...
Hello T. Vallko, Thanks for all your help so far, the link you provided
was
very helpful.

I was able to get this formula to work

=SUMPRODUCT((Master!E3:E800="Blue")*(Master!I3:I80 0="frisbee"))

But now I have run into another problem (will it never end??).

When "frisbee" is the result of another formula the SUMPRODUCT formula
doesn't work. How can I get the SUMPRODUCT formula to work based on the
value of "frisbee" when frisbee is the result of a formula?

Thanks


"ksean" wrote:

I used your exact formula and it didn't work then I assumed the -- meant
needed to add in where the -- was

1st =SUMPRODUCT(--(Master!D1:D10="blue"),--(Master!E1:E10="frisbee"))
2nd
=SUMPRODUCT((countif(Master!D1:D10="blue")),(count if(Master!E1:E10="frisbee")))

Kerry


"T. Valko" wrote:

I can't get your formula to work

Kind of vague. What exactly does that mean? You get an error? You get
the
wrong result? You don't get any result? Your hard drive crashed?

Post the *exact* formula you tried.

what does the -- stand for?

See this:

http://xldynamic.com/source/xld.SUMPRODUCT.html

--
Biff
Microsoft Excel MVP


"ksean" wrote in message
...
I can't get your formula to work as you have shown it, in your formula
what
does the -- stand for? I am still missing something!



"T. Valko" wrote:

Try this...

=SUMPRODUCT(--(Master!D1:D10="blue"),--(Master!E1:E10="frisbee"))

Better to use cells to hold the criteria:

A1 = blue
B1 = frisbee

=SUMPRODUCT(--(Master!D1:D10=A1),--(Master!E1:E10=B1))

--
Biff
Microsoft Excel MVP


"ksean" wrote in message
...
Thanks it worked great however now I need to expand the search
across 2
columns on a different sheet.

For example:
I need cell B5 on sheet 'stock' to tell me how many times column D
on
sheet
'Master' displays "blue" and column E displays "frisbee". I am
trying
to
determine how many times "blue" and "frisbee" appear on the same
row
side
by
side.

Thanks,
Kerry



"T. Valko" wrote:

Try this:

=COUNTIF(A1:A300,"A1")

--
Biff
Microsoft Excel MVP


"ksean" wrote in message
...
Is there a way to use a formula to determine how many times an
alphanumeric
value is used in a column of cells.

For example a column consisting of 300 cells that have one of
the
folloing
notations in each cell "A1, A2, B1, B2, B3, C1, C2 or C3".

Can a formula be used to determine how many times A2 appears in
the
column
if so what formula would I use?

Can a formula be used to determine how many times A1 or B3 or
C2
appears
in
the column if so what formula would I use?


.



.



.



.


--

Dave Peterson
.


--

Dave Peterson

Here's a few mo

=SUMPRODUCT(--(ISNUMBER(SEARCH("blue_frisbee",Master!E3:E20&"_"& Master!I3:I20))))

=SUMPRODUCT(--(ISNUMBER(SEARCH("blue",Master!E3:E20))),--(ISNUMBER(SEARCH("frisbee",Master!I3:I20))))

This one is array entered** :

=SUM(IF(ISTEXT(Master!E3:E20),IF(Master!E3:E20="bl ue",IF(ISTEXT(Master!I3:I20),IF(Master!I3:I20="fri sbee",1)))))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Dave Peterson" wrote in message
...
=SUM(if(isna(Master!E3:E800),0,(master!e3:e800="Bl ue"))
*IF(ISNA(Master!I3:I800),0,(Master!I3:I800="Frisbe e")))

Still array entered.

If you look close enough, you'll see that there's an if/then/else patthern
in
each portion of the formula. Use the if() to look for an error. If it's
an
error, use 0. Else use the array of true/falses created by:
(master!e3:e800="Blue").

You'll just keep adding to that pattern.

ksean wrote:

Biff and Dave,

Both your formulas worked great providing the #N/A is only in one column
but
when I tried to adapt the formula for use in another spreadsheet it
returned
a #N/A value. I discovered that in the second spreadsheet there are rows
where both columns E & I have #N/A in them.

Any thoughts on how to get around this?

What is the possibility of adding a third and forth search criteria?

Kerry

"Dave Peterson" wrote:

Another way is to use an array entered formula.

=SUM((Master!E3:E800="Blue")
*IF(ISNA(Master!I3:I800),0,(Master!I3:I800="Frisbe e")))

This is an array formula. Hit ctrl-shift-enter instead of enter. If
you do it
correctly, excel will wrap curly brackets {} around your formula.
(don't type
them yourself.)

The difference between this suggestion and Biff's is how something
like:

xxxxFrisbeeyyyy
is treated in the count.

This =sum() array formula will ignore this entry.

Biff's =isnumber(search(...)) version will include it.





ksean wrote:

Ok... I figured out what the problem is.

Both you and Dave Peterson are correct the formula in the frisbee
column is
not the problem.

The problem is that there are several cells in the frisbee column
where the
formula in the cell has returned a #N/A . The formula that we have
been
working to create does not like this #N/A.

Do you have any ideas on how to work around the #N/A ?

"T. Valko" wrote:

What does the formula that returns frisbee look like?

If the SUMPRODUCT isn't counting "frisbee" then that sounds like
the other
formula returns something along with frisbee, like maybe some kind
of unseen
whitespace character.

Try one of these:

=SUMPRODUCT(--(Master!E3:E800="Blue"),--(TRIM(Master!I3:I800)="frisbee"))

=SUMPRODUCT(--(Master!E3:E800="Blue"),--(ISNUMBER(SEARCH("frisbee",Master!I3:I800))))

--
Biff
Microsoft Excel MVP


"ksean" wrote in message
...
Hello T. Vallko, Thanks for all your help so far, the link you
provided
was
very helpful.

I was able to get this formula to work

=SUMPRODUCT((Master!E3:E800="Blue")*(Master!I3:I80 0="frisbee"))

But now I have run into another problem (will it never end??).

When "frisbee" is the result of another formula the SUMPRODUCT
formula
doesn't work. How can I get the SUMPRODUCT formula to work based
on the
value of "frisbee" when frisbee is the result of a formula?

Thanks


"ksean" wrote:

I used your exact formula and it didn't work then I assumed
the -- meant
needed to add in where the -- was

1st
=SUMPRODUCT(--(Master!D1:D10="blue"),--(Master!E1:E10="frisbee"))
2nd
=SUMPRODUCT((countif(Master!D1:D10="blue")),(count if(Master!E1:E10="frisbee")))

Kerry


"T. Valko" wrote:

I can't get your formula to work

Kind of vague. What exactly does that mean? You get an error?
You get
the
wrong result? You don't get any result? Your hard drive
crashed?

Post the *exact* formula you tried.

what does the -- stand for?

See this:

http://xldynamic.com/source/xld.SUMPRODUCT.html

--
Biff
Microsoft Excel MVP


"ksean" wrote in message
...
I can't get your formula to work as you have shown it, in
your formula
what
does the -- stand for? I am still missing something!



"T. Valko" wrote:

Try this...

=SUMPRODUCT(--(Master!D1:D10="blue"),--(Master!E1:E10="frisbee"))

Better to use cells to hold the criteria:

A1 = blue
B1 = frisbee

=SUMPRODUCT(--(Master!D1:D10=A1),--(Master!E1:E10=B1))

--
Biff
Microsoft Excel MVP


"ksean" wrote in message
...
Thanks it worked great however now I need to expand the
search
across 2
columns on a different sheet.

For example:
I need cell B5 on sheet 'stock' to tell me how many times
column D
on
sheet
'Master' displays "blue" and column E displays "frisbee".
I am
trying
to
determine how many times "blue" and "frisbee" appear on
the same
row
side
by
side.

Thanks,
Kerry



"T. Valko" wrote:

Try this:

=COUNTIF(A1:A300,"A1")

--
Biff
Microsoft Excel MVP


"ksean" wrote in
message
...
Is there a way to use a formula to determine how many
times an
alphanumeric
value is used in a column of cells.

For example a column consisting of 300 cells that have
one of
the
folloing
notations in each cell "A1, A2, B1, B2, B3, C1, C2 or
C3".

Can a formula be used to determine how many times A2
appears in
the
column
if so what formula would I use?

Can a formula be used to determine how many times A1
or B3 or
C2
appears
in
the column if so what formula would I use?


.



.



.



.


--

Dave Peterson
.


--

Dave Peterson

Dave,

This worked great.

Thanks
Kerry



"Dave Peterson" wrote:

=SUM(if(isna(Master!E3:E800),0,(master!e3:e800="Bl ue"))
*IF(ISNA(Master!I3:I800),0,(Master!I3:I800="Frisbe e")))

Still array entered.

If you look close enough, you'll see that there's an if/then/else patthern in
each portion of the formula. Use the if() to look for an error. If it's an
error, use 0. Else use the array of true/falses created by:
(master!e3:e800="Blue").

You'll just keep adding to that pattern.

ksean wrote:

Biff and Dave,

Both your formulas worked great providing the #N/A is only in one column but
when I tried to adapt the formula for use in another spreadsheet it returned
a #N/A value. I discovered that in the second spreadsheet there are rows
where both columns E & I have #N/A in them.

Any thoughts on how to get around this?

What is the possibility of adding a third and forth search criteria?

Kerry

"Dave Peterson" wrote:

Another way is to use an array entered formula.

=SUM((Master!E3:E800="Blue")
*IF(ISNA(Master!I3:I800),0,(Master!I3:I800="Frisbe e")))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

The difference between this suggestion and Biff's is how something like:

xxxxFrisbeeyyyy
is treated in the count.

This =sum() array formula will ignore this entry.

Biff's =isnumber(search(...)) version will include it.





ksean wrote:

Ok... I figured out what the problem is.

Both you and Dave Peterson are correct the formula in the frisbee column is
not the problem.

The problem is that there are several cells in the frisbee column where the
formula in the cell has returned a #N/A . The formula that we have been
working to create does not like this #N/A.

Do you have any ideas on how to work around the #N/A ?

"T. Valko" wrote:

What does the formula that returns frisbee look like?

If the SUMPRODUCT isn't counting "frisbee" then that sounds like the other
formula returns something along with frisbee, like maybe some kind of unseen
whitespace character.

Try one of these:

=SUMPRODUCT(--(Master!E3:E800="Blue"),--(TRIM(Master!I3:I800)="frisbee"))

=SUMPRODUCT(--(Master!E3:E800="Blue"),--(ISNUMBER(SEARCH("frisbee",Master!I3:I800))))

--
Biff
Microsoft Excel MVP


"ksean" wrote in message
...
Hello T. Vallko, Thanks for all your help so far, the link you provided
was
very helpful.

I was able to get this formula to work

=SUMPRODUCT((Master!E3:E800="Blue")*(Master!I3:I80 0="frisbee"))

But now I have run into another problem (will it never end??).

When "frisbee" is the result of another formula the SUMPRODUCT formula
doesn't work. How can I get the SUMPRODUCT formula to work based on the
value of "frisbee" when frisbee is the result of a formula?

Thanks


"ksean" wrote:

I used your exact formula and it didn't work then I assumed the -- meant
needed to add in where the -- was

1st =SUMPRODUCT(--(Master!D1:D10="blue"),--(Master!E1:E10="frisbee"))
2nd
=SUMPRODUCT((countif(Master!D1:D10="blue")),(count if(Master!E1:E10="frisbee")))

Kerry


"T. Valko" wrote:

I can't get your formula to work

Kind of vague. What exactly does that mean? You get an error? You get
the
wrong result? You don't get any result? Your hard drive crashed?

Post the *exact* formula you tried.

what does the -- stand for?

See this:

http://xldynamic.com/source/xld.SUMPRODUCT.html

--
Biff
Microsoft Excel MVP


"ksean" wrote in message
...
I can't get your formula to work as you have shown it, in your formula
what
does the -- stand for? I am still missing something!



"T. Valko" wrote:

Try this...

=SUMPRODUCT(--(Master!D1:D10="blue"),--(Master!E1:E10="frisbee"))

Better to use cells to hold the criteria:

A1 = blue
B1 = frisbee

=SUMPRODUCT(--(Master!D1:D10=A1),--(Master!E1:E10=B1))

--
Biff
Microsoft Excel MVP


"ksean" wrote in message
...
Thanks it worked great however now I need to expand the search
across 2
columns on a different sheet.

For example:
I need cell B5 on sheet 'stock' to tell me how many times column D
on
sheet
'Master' displays "blue" and column E displays "frisbee". I am
trying
to
determine how many times "blue" and "frisbee" appear on the same
row
side
by
side.

Thanks,
Kerry



"T. Valko" wrote:

Try this:

=COUNTIF(A1:A300,"A1")

--
Biff
Microsoft Excel MVP


"ksean" wrote in message
...
Is there a way to use a formula to determine how many times an
alphanumeric
value is used in a column of cells.

For example a column consisting of 300 cells that have one of
the
folloing
notations in each cell "A1, A2, B1, B2, B3, C1, C2 or C3".

Can a formula be used to determine how many times A2 appears in
the
column
if so what formula would I use?

Can a formula be used to determine how many times A1 or B3 or
C2
appears
in
the column if so what formula would I use?


.



.



.



.


--

Dave Peterson
.


--

Dave Peterson
.

Biff,

The result of the formula should be 85

SUMPRODUCT(--(ISNUMBER(SEARCH("blue_frisbee",Master!E3:E800&"_" &Master!F3:F800)))) returned a result of 129, I can't figure out why.

SUMPRODUCT(--(ISNUMBER(SEARCH("blue",Master!E3:E800))),--(ISNUMBER(SEARCH("frisbee",Master!F3:F800))))
also returned a result of 129, I can't figure out why.

SUM(IF(ISTEXT(Master!E3:E800),IF(Master!E3:E800="b lue",IF(ISTEXT(Master!F3:F800),IF(Master!F3:F800=" frisbee",1)))))
returned a result of 85, which is correct.

By the way in the first two formulas you used a -- in the formula, what
does it do?

Thanks
Kerry




"T. Valko" wrote:

Here's a few mo

=SUMPRODUCT(--(ISNUMBER(SEARCH("blue_frisbee",Master!E3:E20&"_"& Master!I3:I20))))

=SUMPRODUCT(--(ISNUMBER(SEARCH("blue",Master!E3:E20))),--(ISNUMBER(SEARCH("frisbee",Master!I3:I20))))

This one is array entered** :

=SUM(IF(ISTEXT(Master!E3:E20),IF(Master!E3:E20="bl ue",IF(ISTEXT(Master!I3:I20),IF(Master!I3:I20="fri sbee",1)))))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Dave Peterson" wrote in message
...
=SUM(if(isna(Master!E3:E800),0,(master!e3:e800="Bl ue"))
*IF(ISNA(Master!I3:I800),0,(Master!I3:I800="Frisbe e")))

Still array entered.

If you look close enough, you'll see that there's an if/then/else patthern
in
each portion of the formula. Use the if() to look for an error. If it's
an
error, use 0. Else use the array of true/falses created by:
(master!e3:e800="Blue").

You'll just keep adding to that pattern.

ksean wrote:

Biff and Dave,

Both your formulas worked great providing the #N/A is only in one column
but
when I tried to adapt the formula for use in another spreadsheet it
returned
a #N/A value. I discovered that in the second spreadsheet there are rows
where both columns E & I have #N/A in them.

Any thoughts on how to get around this?

What is the possibility of adding a third and forth search criteria?

Kerry

"Dave Peterson" wrote:

Another way is to use an array entered formula.

=SUM((Master!E3:E800="Blue")
*IF(ISNA(Master!I3:I800),0,(Master!I3:I800="Frisbe e")))

This is an array formula. Hit ctrl-shift-enter instead of enter. If
you do it
correctly, excel will wrap curly brackets {} around your formula.
(don't type
them yourself.)

The difference between this suggestion and Biff's is how something
like:

xxxxFrisbeeyyyy
is treated in the count.

This =sum() array formula will ignore this entry.

Biff's =isnumber(search(...)) version will include it.





ksean wrote:

Ok... I figured out what the problem is.

Both you and Dave Peterson are correct the formula in the frisbee
column is
not the problem.

The problem is that there are several cells in the frisbee column
where the
formula in the cell has returned a #N/A . The formula that we have
been
working to create does not like this #N/A.

Do you have any ideas on how to work around the #N/A ?

"T. Valko" wrote:

What does the formula that returns frisbee look like?

If the SUMPRODUCT isn't counting "frisbee" then that sounds like
the other
formula returns something along with frisbee, like maybe some kind
of unseen
whitespace character.

Try one of these:

=SUMPRODUCT(--(Master!E3:E800="Blue"),--(TRIM(Master!I3:I800)="frisbee"))

=SUMPRODUCT(--(Master!E3:E800="Blue"),--(ISNUMBER(SEARCH("frisbee",Master!I3:I800))))

--
Biff
Microsoft Excel MVP


"ksean" wrote in message
...
Hello T. Vallko, Thanks for all your help so far, the link you
provided
was
very helpful.

I was able to get this formula to work

=SUMPRODUCT((Master!E3:E800="Blue")*(Master!I3:I80 0="frisbee"))

But now I have run into another problem (will it never end??).

When "frisbee" is the result of another formula the SUMPRODUCT
formula
doesn't work. How can I get the SUMPRODUCT formula to work based
on the
value of "frisbee" when frisbee is the result of a formula?

Thanks


"ksean" wrote:

I used your exact formula and it didn't work then I assumed
the -- meant
needed to add in where the -- was

1st
=SUMPRODUCT(--(Master!D1:D10="blue"),--(Master!E1:E10="frisbee"))
2nd
=SUMPRODUCT((countif(Master!D1:D10="blue")),(count if(Master!E1:E10="frisbee")))

Kerry


"T. Valko" wrote:

I can't get your formula to work

Kind of vague. What exactly does that mean? You get an error?
You get
the
wrong result? You don't get any result? Your hard drive
crashed?

Post the *exact* formula you tried.

what does the -- stand for?

See this:

http://xldynamic.com/source/xld.SUMPRODUCT.html

--
Biff
Microsoft Excel MVP


"ksean" wrote in message
...
I can't get your formula to work as you have shown it, in
your formula
what
does the -- stand for? I am still missing something!



"T. Valko" wrote:

Try this...

=SUMPRODUCT(--(Master!D1:D10="blue"),--(Master!E1:E10="frisbee"))

Better to use cells to hold the criteria:

A1 = blue
B1 = frisbee

=SUMPRODUCT(--(Master!D1:D10=A1),--(Master!E1:E10=B1))

--
Biff
Microsoft Excel MVP


"ksean" wrote in message
...
Thanks it worked great however now I need to expand the
search
across 2
columns on a different sheet.

For example:
I need cell B5 on sheet 'stock' to tell me how many times
column D
on
sheet
'Master' displays "blue" and column E displays "frisbee".
I am
trying
to
determine how many times "blue" and "frisbee" appear on
the same
row
side
by
side.

Thanks,
Kerry



"T. Valko" wrote:

Try this:

=COUNTIF(A1:A300,"A1")

--
Biff
Microsoft Excel MVP


"ksean" wrote in
message
...
Is there a way to use a formula to determine how many
times an
alphanumeric
value is used in a column of cells.

For example a column consisting of 300 cells that have
one of
the
folloing
notations in each cell "A1, A2, B1, B2, B3, C1, C2 or
C3".

Can a formula be used to determine how many times A2
appears in
the
column
if so what formula would I use?

Can a formula be used to determine how many times A1
or B3 or
C2
appears
in
the column if so what formula would I use?


.



.



.



.


--

Dave Peterson
.


--

Dave Peterson


.

Hard to say why you're getting incorrect results.

Here's a very small sample file that demonstrates those formulas and they do
return the correct results.

xKSEAN.xls 14kb

http://cjoint.com/?kDrZi15bnj

For an explanation of the double unary (--):

http://xldynamic.com/source/xld.SUMPRODUCT.html

--
Biff
Microsoft Excel MVP


"ksean" wrote in message
...
Biff,

The result of the formula should be 85

SUMPRODUCT(--(ISNUMBER(SEARCH("blue_frisbee",Master!E3:E800&"_" &Master!F3:F800))))
returned a result of 129, I can't figure out why.

SUMPRODUCT(--(ISNUMBER(SEARCH("blue",Master!E3:E800))),--(ISNUMBER(SEARCH("frisbee",Master!F3:F800))))
also returned a result of 129, I can't figure out why.

SUM(IF(ISTEXT(Master!E3:E800),IF(Master!E3:E800="b lue",IF(ISTEXT(Master!F3:F800),IF(Master!F3:F800=" frisbee",1)))))
returned a result of 85, which is correct.

By the way in the first two formulas you used a -- in the formula, what
does it do?

Thanks
Kerry




"T. Valko" wrote:

Here's a few mo

=SUMPRODUCT(--(ISNUMBER(SEARCH("blue_frisbee",Master!E3:E20&"_"& Master!I3:I20))))

=SUMPRODUCT(--(ISNUMBER(SEARCH("blue",Master!E3:E20))),--(ISNUMBER(SEARCH("frisbee",Master!I3:I20))))

This one is array entered** :

=SUM(IF(ISTEXT(Master!E3:E20),IF(Master!E3:E20="bl ue",IF(ISTEXT(Master!I3:I20),IF(Master!I3:I20="fri sbee",1)))))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Dave Peterson" wrote in message
...
=SUM(if(isna(Master!E3:E800),0,(master!e3:e800="Bl ue"))
*IF(ISNA(Master!I3:I800),0,(Master!I3:I800="Frisbe e")))

Still array entered.

If you look close enough, you'll see that there's an if/then/else
patthern
in
each portion of the formula. Use the if() to look for an error. If
it's
an
error, use 0. Else use the array of true/falses created by:
(master!e3:e800="Blue").

You'll just keep adding to that pattern.

ksean wrote:

Biff and Dave,

Both your formulas worked great providing the #N/A is only in one
column
but
when I tried to adapt the formula for use in another spreadsheet it
returned
a #N/A value. I discovered that in the second spreadsheet there are
rows
where both columns E & I have #N/A in them.

Any thoughts on how to get around this?

What is the possibility of adding a third and forth search criteria?

Kerry

"Dave Peterson" wrote:

Another way is to use an array entered formula.

=SUM((Master!E3:E800="Blue")
*IF(ISNA(Master!I3:I800),0,(Master!I3:I800="Frisbe e")))

This is an array formula. Hit ctrl-shift-enter instead of enter.
If
you do it
correctly, excel will wrap curly brackets {} around your formula.
(don't type
them yourself.)

The difference between this suggestion and Biff's is how something
like:

xxxxFrisbeeyyyy
is treated in the count.

This =sum() array formula will ignore this entry.

Biff's =isnumber(search(...)) version will include it.





ksean wrote:

Ok... I figured out what the problem is.

Both you and Dave Peterson are correct the formula in the frisbee
column is
not the problem.

The problem is that there are several cells in the frisbee column
where the
formula in the cell has returned a #N/A . The formula that we
have
been
working to create does not like this #N/A.

Do you have any ideas on how to work around the #N/A ?

"T. Valko" wrote:

What does the formula that returns frisbee look like?

If the SUMPRODUCT isn't counting "frisbee" then that sounds like
the other
formula returns something along with frisbee, like maybe some
kind
of unseen
whitespace character.

Try one of these:

=SUMPRODUCT(--(Master!E3:E800="Blue"),--(TRIM(Master!I3:I800)="frisbee"))

=SUMPRODUCT(--(Master!E3:E800="Blue"),--(ISNUMBER(SEARCH("frisbee",Master!I3:I800))))

--
Biff
Microsoft Excel MVP


"ksean" wrote in message
...
Hello T. Vallko, Thanks for all your help so far, the link you
provided
was
very helpful.

I was able to get this formula to work

=SUMPRODUCT((Master!E3:E800="Blue")*(Master!I3:I80 0="frisbee"))

But now I have run into another problem (will it never end??).

When "frisbee" is the result of another formula the SUMPRODUCT
formula
doesn't work. How can I get the SUMPRODUCT formula to work
based
on the
value of "frisbee" when frisbee is the result of a formula?

Thanks


"ksean" wrote:

I used your exact formula and it didn't work then I assumed
the -- meant
needed to add in where the -- was

1st
=SUMPRODUCT(--(Master!D1:D10="blue"),--(Master!E1:E10="frisbee"))
2nd
=SUMPRODUCT((countif(Master!D1:D10="blue")),(count if(Master!E1:E10="frisbee")))

Kerry


"T. Valko" wrote:

I can't get your formula to work

Kind of vague. What exactly does that mean? You get an
error?
You get
the
wrong result? You don't get any result? Your hard drive
crashed?

Post the *exact* formula you tried.

what does the -- stand for?

See this:

http://xldynamic.com/source/xld.SUMPRODUCT.html

--
Biff
Microsoft Excel MVP


"ksean" wrote in message
...
I can't get your formula to work as you have shown it, in
your formula
what
does the -- stand for? I am still missing something!



"T. Valko" wrote:

Try this...

=SUMPRODUCT(--(Master!D1:D10="blue"),--(Master!E1:E10="frisbee"))

Better to use cells to hold the criteria:

A1 = blue
B1 = frisbee

=SUMPRODUCT(--(Master!D1:D10=A1),--(Master!E1:E10=B1))

--
Biff
Microsoft Excel MVP


"ksean" wrote in
message
...
Thanks it worked great however now I need to expand
the
search
across 2
columns on a different sheet.

For example:
I need cell B5 on sheet 'stock' to tell me how many
times
column D
on
sheet
'Master' displays "blue" and column E displays
"frisbee".
I am
trying
to
determine how many times "blue" and "frisbee" appear
on
the same
row
side
by
side.

Thanks,
Kerry



"T. Valko" wrote:

Try this:

=COUNTIF(A1:A300,"A1")

--
Biff
Microsoft Excel MVP


"ksean" wrote in
message
...
Is there a way to use a formula to determine how
many
times an
alphanumeric
value is used in a column of cells.

For example a column consisting of 300 cells that
have
one of
the
folloing
notations in each cell "A1, A2, B1, B2, B3, C1, C2
or
C3".

Can a formula be used to determine how many times
A2
appears in
the
column
if so what formula would I use?

Can a formula be used to determine how many times
A1
or B3 or
C2
appears
in
the column if so what formula would I use?


.



.



.



.


--

Dave Peterson
.


--

Dave Peterson


.