I have several sheets in my workbook that we use to count different product
movements. First formula is in A5 the second is in A6
=SUMPRODUCT(('JAN 07'!$B$5:$B$245="ASPHALT")*(JAN 07'!$F$5:$F$245="L"))
=SUMPRODUCT(('JAN 07'!$B$5:$B$245="ASPHALT")*(JAN 07'!$F$5:$F$245="P"))

This worked fine for us in past years now management wants to just count
ASPHALT total handlings without being specfic and I just can not seem to get
it to work. I tried to just get rid of *(JAN 07'!$F$5:$F$245="L")) this part
and all I get is 0 in the cell what am I doing wrong. As well now they are
wanting to group the different products (37 of them) into just 5 grouping and
just count the number of times the groupings were handled because they do not
need individual handling but we have to track them so our sheets are set up
to do that. I've inserted another sheet just for them but I'm having trouble
with getting the grouping down correctly. If I could get the different
products to sum individually on my inserted sheet I can sum them into their
different grouping. I have probably confused you all but I need just a
little help to get me headed in the right direction.

=COUNTIF('JAN 07'!$B$5:$B$245,"ASPHALT")


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"tankerman" wrote in message
...
I have several sheets in my workbook that we use to count different product
movements. First formula is in A5 the second is in A6
=SUMPRODUCT(('JAN 07'!$B$5:$B$245="ASPHALT")*(JAN 07'!$F$5:$F$245="L"))
=SUMPRODUCT(('JAN 07'!$B$5:$B$245="ASPHALT")*(JAN 07'!$F$5:$F$245="P"))

This worked fine for us in past years now management wants to just count
ASPHALT total handlings without being specfic and I just can not seem to
get
it to work. I tried to just get rid of *(JAN 07'!$F$5:$F$245="L")) this
part
and all I get is 0 in the cell what am I doing wrong. As well now they are
wanting to group the different products (37 of them) into just 5 grouping
and
just count the number of times the groupings were handled because they do
not
need individual handling but we have to track them so our sheets are set
up
to do that. I've inserted another sheet just for them but I'm having
trouble
with getting the grouping down correctly. If I could get the different
products to sum individually on my inserted sheet I can sum them into
their
different grouping. I have probably confused you all but I need just a
little help to get me headed in the right direction.

or
=SUMPRODUCT(('JAN 07'!$B$5:$B$245="ASPHALT")*1)


--
Don Guillett
SalesAid Software

"tankerman" wrote in message
...
I have several sheets in my workbook that we use to count different product
movements. First formula is in A5 the second is in A6
=SUMPRODUCT(('JAN 07'!$B$5:$B$245="ASPHALT")*(JAN 07'!$F$5:$F$245="L"))
=SUMPRODUCT(('JAN 07'!$B$5:$B$245="ASPHALT")*(JAN 07'!$F$5:$F$245="P"))

This worked fine for us in past years now management wants to just count
ASPHALT total handlings without being specfic and I just can not seem to
get
it to work. I tried to just get rid of *(JAN 07'!$F$5:$F$245="L")) this
part
and all I get is 0 in the cell what am I doing wrong. As well now they are
wanting to group the different products (37 of them) into just 5 grouping
and
just count the number of times the groupings were handled because they do
not
need individual handling but we have to track them so our sheets are set
up
to do that. I've inserted another sheet just for them but I'm having
trouble
with getting the grouping down correctly. If I could get the different
products to sum individually on my inserted sheet I can sum them into
their
different grouping. I have probably confused you all but I need just a
little help to get me headed in the right direction.

Thanks Bob I really appreciate the help

"Bob Phillips" wrote:

=COUNTIF('JAN 07'!$B$5:$B$245,"ASPHALT")


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"tankerman" wrote in message
...
I have several sheets in my workbook that we use to count different product
movements. First formula is in A5 the second is in A6
=SUMPRODUCT(('JAN 07'!$B$5:$B$245="ASPHALT")*(JAN 07'!$F$5:$F$245="L"))
=SUMPRODUCT(('JAN 07'!$B$5:$B$245="ASPHALT")*(JAN 07'!$F$5:$F$245="P"))

This worked fine for us in past years now management wants to just count
ASPHALT total handlings without being specfic and I just can not seem to
get
it to work. I tried to just get rid of *(JAN 07'!$F$5:$F$245="L")) this
part
and all I get is 0 in the cell what am I doing wrong. As well now they are
wanting to group the different products (37 of them) into just 5 grouping
and
just count the number of times the groupings were handled because they do
not
need individual handling but we have to track them so our sheets are set
up
to do that. I've inserted another sheet just for them but I'm having
trouble
with getting the grouping down correctly. If I could get the different
products to sum individually on my inserted sheet I can sum them into
their
different grouping. I have probably confused you all but I need just a
little help to get me headed in the right direction.

Hi,

You can do it with the sumproduct, just add 2 dashes in front like this:

=SUMPRODUCT(--('JAN 07'!$B$5:$B$245="ASPHALT"))

as for the second part, not sure if this will help but if you had say
"asphalt" and "shingles" in one group then you could use something like this:

=SUMPRODUCT(--('JAN 07'!$B$5:$B$245={"ASPHALT","SHINGLES"}))

HTH
Jean-Guy

"tankerman" wrote:

I have several sheets in my workbook that we use to count different product
movements. First formula is in A5 the second is in A6
=SUMPRODUCT(('JAN 07'!$B$5:$B$245="ASPHALT"))
=SUMPRODUCT(('JAN 07'!$B$5:$B$245="ASPHALT")*(JAN 07'!$F$5:$F$245="P"))

This worked fine for us in past years now management wants to just count
ASPHALT total handlings without being specfic and I just can not seem to get
it to work. I tried to just get rid of *(JAN 07'!$F$5:$F$245="L")) this part
and all I get is 0 in the cell what am I doing wrong. As well now they are
wanting to group the different products (37 of them) into just 5 grouping and
just count the number of times the groupings were handled because they do not
need individual handling but we have to track them so our sheets are set up
to do that. I've inserted another sheet just for them but I'm having trouble
with getting the grouping down correctly. If I could get the different
products to sum individually on my inserted sheet I can sum them into their
different grouping. I have probably confused you all but I need just a
little help to get me headed in the right direction.