counting occurrences in another sheet based on several criteria
 

i need to count data from sheet "A" based on several criteria and enter it on
sheet "B".

Sheet A:

a b c
d
1 site item received
billed
2 one server 2
1
3 one 370 1
2
4 one 470 1
1
5 two server 1
1

For this example, i need to count only the total quantity of items both
billed and received for any occurrence of either "server", "370", or "470"
listed in sheet A where the "site" is listed as "one".

For this example, the formula's result would be 3, since only 3 were billed
and received for site one.

PLEASE HELP! Counting all this data manually for over 100 items and over 30
projects is taking me WEEKS to complete.

Thank you!

Mike

counting occurrences in another sheet based on several criteria
 

Damn. it messed up the format of my text in the table example i created. i
corrected it in this reply since i don't know how to edit a post and repost
it.

PLEASE HELP!
Thanks!
Mike

"WiFiMike2006" wrote:

i need to count data from sheet "A" based on several criteria and enter it on
sheet "B".

Sheet A:

a b c d
1 site item received billed
2 one server 2 1
3 one 370 1 2
4 one 470 1 1
5 two server 1 1

For this example, i need to count only the total quantity of items both
billed and received for any occurrence of either "server", "370", or "470"
listed in sheet A where the "site" is listed as "one".

For this example, the formula's result would be 3, since only 3 were billed
and received for site one.

PLEASE HELP! Counting all this data manually for over 100 items and over 30
projects is taking me WEEKS to complete.

Thank you!

Mike

counting occurrences in another sheet based on several criteria
 

Try this:

items both billed and received

I'm assuming that means both fields have an entry.

=SUMPRODUCT(--(A2:A5="one"),--(ISNUMBER(MATCH(B2:B5,{"server",370,470},0))),--(C2:C5<""),--(D2:D5<""))

Biff

"WiFiMike2006" wrote in message
...
i need to count data from sheet "A" based on several criteria and enter it
on
sheet "B".

Sheet A:

a b c
d
1 site item received
billed
2 one server 2
1
3 one 370 1
2
4 one 470 1
1
5 two server 1
1

For this example, i need to count only the total quantity of items both
billed and received for any occurrence of either "server", "370", or "470"
listed in sheet A where the "site" is listed as "one".

For this example, the formula's result would be 3, since only 3 were
billed
and received for site one.

PLEASE HELP! Counting all this data manually for over 100 items and over
30
projects is taking me WEEKS to complete.

Thank you!

Mike

counting occurrences in another sheet based on several criteria
 

Try this:

=SUMPRODUCT((SheetA!A2:A10="one")*(SheetA!B2:B10={ "server",370,470})*(SheetA!C2:C10+SheetA!D2:D1 0))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"WiFiMike2006" wrote in message
...
i need to count data from sheet "A" based on several criteria and enter it
on
sheet "B".

Sheet A:

a b c
d
1 site item received
billed
2 one server 2
1
3 one 370 1
2
4 one 470 1
1
5 two server 1
1

For this example, i need to count only the total quantity of items both
billed and received for any occurrence of either "server", "370", or "470"
listed in sheet A where the "site" is listed as "one".

For this example, the formula's result would be 3, since only 3 were
billed
and received for site one.

PLEASE HELP! Counting all this data manually for over 100 items and over
30
projects is taking me WEEKS to complete.

Thank you!

Mike

counting occurrences in another sheet based on several criteri
 

Cool. THANK YOU!

Just one more question, can I use a wildcard to search for text *contained*
in the item list in this formula? For example:

=SUMPRODUCT((SheetA!A2:A10="one")*(SheetA!B2:B10={ "*server*","*370*","*470*"})*(SheetA!C2:C10+SheetA !D2:D10))

....because, in reality, the items column is a bunch of item descriptions
with stuff like "Dell Precision 370" or "Dell server quote" in them. They
aren't as simple as just a one word description.

Also, I need to test this formula you gave still. I'm not sure, but it looks
like it will add the received and billed quantities for each item, instead of
adding quantity of items billed that were also received. For example, if
received = 0, and billed = 2, i wouldn't want it to count 2, I would want it
to count 0. If received = 2 and billed = 1, it should count 1. If received =
4 and billed = 2, it should count 2. Basically, it should count quantity
billed that are also received. Understand?

Thanks,
Mike

"RagDyer" wrote:

Try this:

=SUMPRODUCT((SheetA!A2:A10="one")*(SheetA!B2:B10={ "server",370,470})*(SheetA!C2:C10+SheetA!D2:D1 0))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"WiFiMike2006" wrote in message
...
i need to count data from sheet "A" based on several criteria and enter it
on
sheet "B".

Sheet A:

a b c
d
1 site item received
billed
2 one server 2
1
3 one 370 1
2
4 one 470 1
1
5 two server 1
1

For this example, i need to count only the total quantity of items both
billed and received for any occurrence of either "server", "370", or "470"
listed in sheet A where the "site" is listed as "one".

For this example, the formula's result would be 3, since only 3 were
billed
and received for site one.

PLEASE HELP! Counting all this data manually for over 100 items and over
30
projects is taking me WEEKS to complete.

Thank you!

Mike

counting occurrences in another sheet based on several criteri
 

Is this "Driller" ?

<G

Biff

"WiFiMike2006" wrote in message
...
Cool. THANK YOU!

Just one more question, can I use a wildcard to search for text
*contained*
in the item list in this formula? For example:

=SUMPRODUCT((SheetA!A2:A10="one")*(SheetA!B2:B10={ "*server*","*370*","*470*"})*(SheetA!C2:C10+SheetA !D2:D10))

...because, in reality, the items column is a bunch of item descriptions
with stuff like "Dell Precision 370" or "Dell server quote" in them. They
aren't as simple as just a one word description.

Also, I need to test this formula you gave still. I'm not sure, but it
looks
like it will add the received and billed quantities for each item, instead
of
adding quantity of items billed that were also received. For example, if
received = 0, and billed = 2, i wouldn't want it to count 2, I would want
it
to count 0. If received = 2 and billed = 1, it should count 1. If
received =
4 and billed = 2, it should count 2. Basically, it should count quantity
billed that are also received. Understand?

Thanks,
Mike

"RagDyer" wrote:

Try this:

=SUMPRODUCT((SheetA!A2:A10="one")*(SheetA!B2:B10={ "server",370,470})*(SheetA!C2:C10+SheetA!D2:D1 0))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"WiFiMike2006" wrote in message
...
i need to count data from sheet "A" based on several criteria and enter
it
on
sheet "B".

Sheet A:

a b c
d
1 site item received
billed
2 one server 2
1
3 one 370 1
2
4 one 470 1
1
5 two server 1
1

For this example, i need to count only the total quantity of items both
billed and received for any occurrence of either "server", "370", or
"470"
listed in sheet A where the "site" is listed as "one".

For this example, the formula's result would be 3, since only 3 were
billed
and received for site one.

PLEASE HELP! Counting all this data manually for over 100 items and
over
30
projects is taking me WEEKS to complete.

Thank you!

Mike

counting occurrences in another sheet based on several criteri
 

billed and received may not both have a quantity entered. either/or could be
blank. but the idea is to count items billed that were also received. so if
billed is 2 and received is 4, only a count of 2 should be returned.

also, i think you might have forgotten the sheet! reference in the formula
since the data will be pulled from a different sheet (named "POs").

and one other thing. i need to search for text *contained* in the items list
since the list is item descriptions like "dell server" or "dell precision
370". the items won't be an exact match to the text string. Can wildcards be
used in this formula?

Please respond.

Thank you,
Mike

"T. Valko" wrote:

Try this:

items both billed and received

I'm assuming that means both fields have an entry.

=SUMPRODUCT(--(A2:A5="one"),--(ISNUMBER(MATCH(B2:B5,{"server",370,470},0))),--(C2:C5<""),--(D2:D5<""))

Biff

"WiFiMike2006" wrote in message
...
i need to count data from sheet "A" based on several criteria and enter it
on
sheet "B".

Sheet A:

a b c
d
1 site item received
billed
2 one server 2
1
3 one 370 1
2
4 one 470 1
1
5 two server 1
1

For this example, i need to count only the total quantity of items both
billed and received for any occurrence of either "server", "370", or "470"
listed in sheet A where the "site" is listed as "one".

For this example, the formula's result would be 3, since only 3 were
billed
and received for site one.

PLEASE HELP! Counting all this data manually for over 100 items and over
30
projects is taking me WEEKS to complete.

Thank you!

Mike

counting occurrences in another sheet based on several criteri
 

I don't know who Driller is, but I'm betting he knows a lot more than I do
about excel. I know this one is seriously complicated. I'm just hoping there
is some way to do it.

Here's is the actual formula as I'm trying it now, with correct ranges and
sheet references:

=SUMPRODUCT((POs!B2:B2000="century
city")*(POs!H2:H2000={"*server*","*370*","*470*"}) *(POs!K2:K2000+POs!L2:L2000))

I also tried:

=SUMPRODUCT(--(POs!B2:B2000="century
city"),--(ISNUMBER(MATCH(POs!H2:H2000,{"*server*","*370*"," *470*"},0))),--(POs!K2:K2000<""),--(POs!L2:L2000<""))

Unfortunately, neither one is working.

Maybe if I describe what I need the formula to do in steps it will make it
easier to figure out what I need:

1. filter results where B2:B2000 matches "century city".
2. narrow those results by H2:H2000 *contains* the text "server", "370", or
"470".
3. out of those items, add up only the quantity billed in L2:L2000 that is
also shown as received in K2:K2000. This last step may require some <=/=,
IF(AND type of stuff to only count the quantity in column L that also appears
in column K. But I really have no idea.

If it would help, I could email you a sample of the spreadsheet. Just let me
know where to email it.

I know this is a serious brain cruncher. But the last time I had to go
through and add up all this data manually because I couldn't figure out a
formula to do it, it took me over 2 weeks, on top of all the other work I
have to do. There are over 100 items on over 30 projects that I have to count
billed-and-also-received items for on the PO report. So if it makes you feel
any better about solving this one, you'll allow me to spend less hours at
work, thereby giving me more time with my kids.

Thank you,

Mike


"T. Valko" wrote:

Is this "Driller" ?

<G

Biff

"WiFiMike2006" wrote in message
...
Cool. THANK YOU!

Just one more question, can I use a wildcard to search for text
*contained*
in the item list in this formula? For example:

=SUMPRODUCT((SheetA!A2:A10="one")*(SheetA!B2:B10={ "*server*","*370*","*470*"})*(SheetA!C2:C10+SheetA !D2:D10))

...because, in reality, the items column is a bunch of item descriptions
with stuff like "Dell Precision 370" or "Dell server quote" in them. They
aren't as simple as just a one word description.

Also, I need to test this formula you gave still. I'm not sure, but it
looks
like it will add the received and billed quantities for each item, instead
of
adding quantity of items billed that were also received. For example, if
received = 0, and billed = 2, i wouldn't want it to count 2, I would want
it
to count 0. If received = 2 and billed = 1, it should count 1. If
received =
4 and billed = 2, it should count 2. Basically, it should count quantity
billed that are also received. Understand?

Thanks,
Mike

"RagDyer" wrote:

Try this:

=SUMPRODUCT((SheetA!A2:A10="one")*(SheetA!B2:B10={ "server",370,470})*(SheetA!C2:C10+SheetA!D2:D1 0))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"WiFiMike2006" wrote in message
...
i need to count data from sheet "A" based on several criteria and enter
it
on
sheet "B".

Sheet A:

a b c
d
1 site item received
billed
2 one server 2
1
3 one 370 1
2
4 one 470 1
1
5 two server 1
1

For this example, i need to count only the total quantity of items both
billed and received for any occurrence of either "server", "370", or
"470"
listed in sheet A where the "site" is listed as "one".

For this example, the formula's result would be 3, since only 3 were
billed
and received for site one.

PLEASE HELP! Counting all this data manually for over 100 items and
over
30
projects is taking me WEEKS to complete.

Thank you!

Mike

counting occurrences in another sheet based on several criteri
 

I'm confused about the received/billed stuff.

It sounds like you want a SUM and not a COUNT. Can you post several examples
of the received/billed criteria and what result is expected?

Biff

"WiFiMike2006" wrote in message
...
I don't know who Driller is, but I'm betting he knows a lot more than I do
about excel. I know this one is seriously complicated. I'm just hoping
there
is some way to do it.

Here's is the actual formula as I'm trying it now, with correct ranges and
sheet references:

=SUMPRODUCT((POs!B2:B2000="century
city")*(POs!H2:H2000={"*server*","*370*","*470*"}) *(POs!K2:K2000+POs!L2:L2000))

I also tried:

=SUMPRODUCT(--(POs!B2:B2000="century
city"),--(ISNUMBER(MATCH(POs!H2:H2000,{"*server*","*370*"," *470*"},0))),--(POs!K2:K2000<""),--(POs!L2:L2000<""))

Unfortunately, neither one is working.

Maybe if I describe what I need the formula to do in steps it will make it
easier to figure out what I need:

1. filter results where B2:B2000 matches "century city".
2. narrow those results by H2:H2000 *contains* the text "server", "370",
or
"470".
3. out of those items, add up only the quantity billed in L2:L2000 that is
also shown as received in K2:K2000. This last step may require some <=/=,
IF(AND type of stuff to only count the quantity in column L that also
appears
in column K. But I really have no idea.

If it would help, I could email you a sample of the spreadsheet. Just let
me
know where to email it.

I know this is a serious brain cruncher. But the last time I had to go
through and add up all this data manually because I couldn't figure out a
formula to do it, it took me over 2 weeks, on top of all the other work I
have to do. There are over 100 items on over 30 projects that I have to
count
billed-and-also-received items for on the PO report. So if it makes you
feel
any better about solving this one, you'll allow me to spend less hours at
work, thereby giving me more time with my kids.

Thank you,

Mike


"T. Valko" wrote:

Is this "Driller" ?

<G

Biff

"WiFiMike2006" wrote in message
...
Cool. THANK YOU!

Just one more question, can I use a wildcard to search for text
*contained*
in the item list in this formula? For example:

=SUMPRODUCT((SheetA!A2:A10="one")*(SheetA!B2:B10={ "*server*","*370*","*470*"})*(SheetA!C2:C10+SheetA !D2:D10))

...because, in reality, the items column is a bunch of item
descriptions
with stuff like "Dell Precision 370" or "Dell server quote" in them.
They
aren't as simple as just a one word description.

Also, I need to test this formula you gave still. I'm not sure, but it
looks
like it will add the received and billed quantities for each item,
instead
of
adding quantity of items billed that were also received. For example,
if
received = 0, and billed = 2, i wouldn't want it to count 2, I would
want
it
to count 0. If received = 2 and billed = 1, it should count 1. If
received =
4 and billed = 2, it should count 2. Basically, it should count
quantity
billed that are also received. Understand?

Thanks,
Mike

"RagDyer" wrote:

Try this:

=SUMPRODUCT((SheetA!A2:A10="one")*(SheetA!B2:B10={ "server",370,470})*(SheetA!C2:C10+SheetA!D2:D1 0))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may
benefit !
---------------------------------------------------------------------------
"WiFiMike2006" wrote in
message
...
i need to count data from sheet "A" based on several criteria and
enter
it
on
sheet "B".

Sheet A:

a b c
d
1 site item received
billed
2 one server 2
1
3 one 370 1
2
4 one 470 1
1
5 two server 1
1

For this example, i need to count only the total quantity of items
both
billed and received for any occurrence of either "server", "370", or
"470"
listed in sheet A where the "site" is listed as "one".

For this example, the formula's result would be 3, since only 3 were
billed
and received for site one.

PLEASE HELP! Counting all this data manually for over 100 items and
over
30
projects is taking me WEEKS to complete.

Thank you!

Mike

counting occurrences in another sheet based on several criteri
 

Indeed, i do need a sum of the quantity billed and also received.

Let's assume that these results are all filtered by site "century city", and
filtered by text wildcards "server", "370" and "470" in the item description
column already, and we're just looking at the "qty. received" and "qty.
billed" columns.


____K______L____________________________
1___rec'd___billed____sum returned by formula
2___2______2_______2 (billed = received, qty. of billed summed)
3___3______1_______1 (billed < received, qty. of billed summed)
4___0______2_______0 (billed or received = 0, 0 sum)
5___2______5_______2 (billed received by 3, qty. of received summed)
6___5______0_______2 (billed or received = 0, 0 sum)

with the formula applied, the result for the above example would be 7. this
would be the total amount of items for the given site with any of the
specified text wildcards contained in the item description that were billed,
and also received.

I hope this makes sense and that you are able to solve this problem and let
me know what formula to use to achieve this.

THank you,
Mike

"T. Valko" wrote:

I'm confused about the received/billed stuff.

It sounds like you want a SUM and not a COUNT. Can you post several examples
of the received/billed criteria and what result is expected?

Biff

"WiFiMike2006" wrote in message
...
I don't know who Driller is, but I'm betting he knows a lot more than I do
about excel. I know this one is seriously complicated. I'm just hoping
there
is some way to do it.

Here's is the actual formula as I'm trying it now, with correct ranges and
sheet references:

=SUMPRODUCT((POs!B2:B2000="century
city")*(POs!H2:H2000={"*server*","*370*","*470*"}) *(POs!K2:K2000+POs!L2:L2000))

I also tried:

=SUMPRODUCT(--(POs!B2:B2000="century
city"),--(ISNUMBER(MATCH(POs!H2:H2000,{"*server*","*370*"," *470*"},0))),--(POs!K2:K2000<""),--(POs!L2:L2000<""))

Unfortunately, neither one is working.

Maybe if I describe what I need the formula to do in steps it will make it
easier to figure out what I need:

1. filter results where B2:B2000 matches "century city".
2. narrow those results by H2:H2000 *contains* the text "server", "370",
or
"470".
3. out of those items, add up only the quantity billed in L2:L2000 that is
also shown as received in K2:K2000. This last step may require some <=/=,
IF(AND type of stuff to only count the quantity in column L that also
appears
in column K. But I really have no idea.

If it would help, I could email you a sample of the spreadsheet. Just let
me
know where to email it.

I know this is a serious brain cruncher. But the last time I had to go
through and add up all this data manually because I couldn't figure out a
formula to do it, it took me over 2 weeks, on top of all the other work I
have to do. There are over 100 items on over 30 projects that I have to
count
billed-and-also-received items for on the PO report. So if it makes you
feel
any better about solving this one, you'll allow me to spend less hours at
work, thereby giving me more time with my kids.

Thank you,

Mike


"T. Valko" wrote:

Is this "Driller" ?

<G

Biff

"WiFiMike2006" wrote in message
...
Cool. THANK YOU!

Just one more question, can I use a wildcard to search for text
*contained*
in the item list in this formula? For example:

=SUMPRODUCT((SheetA!A2:A10="one")*(SheetA!B2:B10={ "*server*","*370*","*470*"})*(SheetA!C2:C10+SheetA !D2:D10))

...because, in reality, the items column is a bunch of item
descriptions
with stuff like "Dell Precision 370" or "Dell server quote" in them.
They
aren't as simple as just a one word description.

Also, I need to test this formula you gave still. I'm not sure, but it
looks
like it will add the received and billed quantities for each item,
instead
of
adding quantity of items billed that were also received. For example,
if
received = 0, and billed = 2, i wouldn't want it to count 2, I would
want
it
to count 0. If received = 2 and billed = 1, it should count 1. If
received =
4 and billed = 2, it should count 2. Basically, it should count
quantity
billed that are also received. Understand?

Thanks,
Mike

"RagDyer" wrote:

Try this:

=SUMPRODUCT((SheetA!A2:A10="one")*(SheetA!B2:B10={ "server",370,470})*(SheetA!C2:C10+SheetA!D2:D1 0))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may
benefit !
---------------------------------------------------------------------------
"WiFiMike2006" wrote in
message
...
i need to count data from sheet "A" based on several criteria and
enter
it
on
sheet "B".

Sheet A:

a b c
d
1 site item received
billed
2 one server 2
1
3 one 370 1
2
4 one 470 1
1
5 two server 1
1

For this example, i need to count only the total quantity of items
both
billed and received for any occurrence of either "server", "370", or
"470"
listed in sheet A where the "site" is listed as "one".

For this example, the formula's result would be 3, since only 3 were
billed
and received for site one.

PLEASE HELP! Counting all this data manually for over 100 items and
over
30
projects is taking me WEEKS to complete.

Thank you!

Mike

counting occurrences in another sheet based on several criteri
 

with the formula applied, the result for the above example would be 7.

How about 5?

=SUMPRODUCT((A2:A6="century
city")*(ISNUMBER(SEARCH({"server","370","470"},B2: B6)))*(C2:C60)*(D2:D60)*ABS(C2:C6-D2:D6))

Biff

"WiFiMike2006" wrote in message
...
Indeed, i do need a sum of the quantity billed and also received.

Let's assume that these results are all filtered by site "century city",
and
filtered by text wildcards "server", "370" and "470" in the item
description
column already, and we're just looking at the "qty. received" and "qty.
billed" columns.


____K______L____________________________
1___rec'd___billed____sum returned by formula
2___2______2_______2 (billed = received, qty. of billed summed)
3___3______1_______1 (billed < received, qty. of billed summed)
4___0______2_______0 (billed or received = 0, 0 sum)
5___2______5_______2 (billed received by 3, qty. of received summed)
6___5______0_______2 (billed or received = 0, 0 sum)

with the formula applied, the result for the above example would be 7.
this
would be the total amount of items for the given site with any of the
specified text wildcards contained in the item description that were
billed,
and also received.

I hope this makes sense and that you are able to solve this problem and
let
me know what formula to use to achieve this.

THank you,
Mike

"T. Valko" wrote:

I'm confused about the received/billed stuff.

It sounds like you want a SUM and not a COUNT. Can you post several
examples
of the received/billed criteria and what result is expected?

Biff

"WiFiMike2006" wrote in message
...
I don't know who Driller is, but I'm betting he knows a lot more than I
do
about excel. I know this one is seriously complicated. I'm just hoping
there
is some way to do it.

Here's is the actual formula as I'm trying it now, with correct ranges
and
sheet references:

=SUMPRODUCT((POs!B2:B2000="century
city")*(POs!H2:H2000={"*server*","*370*","*470*"}) *(POs!K2:K2000+POs!L2:L2000))

I also tried:

=SUMPRODUCT(--(POs!B2:B2000="century
city"),--(ISNUMBER(MATCH(POs!H2:H2000,{"*server*","*370*"," *470*"},0))),--(POs!K2:K2000<""),--(POs!L2:L2000<""))

Unfortunately, neither one is working.

Maybe if I describe what I need the formula to do in steps it will make
it
easier to figure out what I need:

1. filter results where B2:B2000 matches "century city".
2. narrow those results by H2:H2000 *contains* the text "server",
"370",
or
"470".
3. out of those items, add up only the quantity billed in L2:L2000 that
is
also shown as received in K2:K2000. This last step may require some
<=/=,
IF(AND type of stuff to only count the quantity in column L that also
appears
in column K. But I really have no idea.

If it would help, I could email you a sample of the spreadsheet. Just
let
me
know where to email it.

I know this is a serious brain cruncher. But the last time I had to go
through and add up all this data manually because I couldn't figure out
a
formula to do it, it took me over 2 weeks, on top of all the other work
I
have to do. There are over 100 items on over 30 projects that I have to
count
billed-and-also-received items for on the PO report. So if it makes you
feel
any better about solving this one, you'll allow me to spend less hours
at
work, thereby giving me more time with my kids.

Thank you,

Mike


"T. Valko" wrote:

Is this "Driller" ?

<G

Biff

"WiFiMike2006" wrote in
message
...
Cool. THANK YOU!

Just one more question, can I use a wildcard to search for text
*contained*
in the item list in this formula? For example:

=SUMPRODUCT((SheetA!A2:A10="one")*(SheetA!B2:B10={ "*server*","*370*","*470*"})*(SheetA!C2:C10+SheetA !D2:D10))

...because, in reality, the items column is a bunch of item
descriptions
with stuff like "Dell Precision 370" or "Dell server quote" in them.
They
aren't as simple as just a one word description.

Also, I need to test this formula you gave still. I'm not sure, but
it
looks
like it will add the received and billed quantities for each item,
instead
of
adding quantity of items billed that were also received. For
example,
if
received = 0, and billed = 2, i wouldn't want it to count 2, I would
want
it
to count 0. If received = 2 and billed = 1, it should count 1. If
received =
4 and billed = 2, it should count 2. Basically, it should count
quantity
billed that are also received. Understand?

Thanks,
Mike

"RagDyer" wrote:

Try this:

=SUMPRODUCT((SheetA!A2:A10="one")*(SheetA!B2:B10={ "server",370,470})*(SheetA!C2:C10+SheetA!D2:D1 0))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may
benefit !
---------------------------------------------------------------------------
"WiFiMike2006" wrote in
message
...
i need to count data from sheet "A" based on several criteria and
enter
it
on
sheet "B".

Sheet A:

a b c
d
1 site item received
billed
2 one server 2
1
3 one 370 1
2
4 one 470 1
1
5 two server 1
1

For this example, i need to count only the total quantity of
items
both
billed and received for any occurrence of either "server", "370",
or
"470"
listed in sheet A where the "site" is listed as "one".

For this example, the formula's result would be 3, since only 3
were
billed
and received for site one.

PLEASE HELP! Counting all this data manually for over 100 items
and
over
30
projects is taking me WEEKS to complete.

Thank you!

Mike

counting occurrences in another sheet based on several criteri
 

Disregard that last formula!

Try this one entered as an array using the key combination of
CTRL,SHIFT,ENTER (not just ENTER):

=SUMPRODUCT((A2:A6="century city")*
(ISNUMBER(SEARCH({"server","370","470"},B2:B6)))*
(C2:C60)*(D2:D60)*
IF(C2:C6=D2:D6,C2:C6,ABS(C2:C6-D2:D6)))

Biff

"T. Valko" wrote in message
...
with the formula applied, the result for the above example would be 7.

How about 5?

=SUMPRODUCT((A2:A6="century
city")*(ISNUMBER(SEARCH({"server","370","470"},B2: B6)))*(C2:C60)*(D2:D60)*ABS(C2:C6-D2:D6))

Biff

"WiFiMike2006" wrote in message
...
Indeed, i do need a sum of the quantity billed and also received.

Let's assume that these results are all filtered by site "century city",
and
filtered by text wildcards "server", "370" and "470" in the item
description
column already, and we're just looking at the "qty. received" and "qty.
billed" columns.


____K______L____________________________
1___rec'd___billed____sum returned by formula
2___2______2_______2 (billed = received, qty. of billed summed)
3___3______1_______1 (billed < received, qty. of billed summed)
4___0______2_______0 (billed or received = 0, 0 sum)
5___2______5_______2 (billed received by 3, qty. of received summed)
6___5______0_______2 (billed or received = 0, 0 sum)

with the formula applied, the result for the above example would be 7.
this
would be the total amount of items for the given site with any of the
specified text wildcards contained in the item description that were
billed,
and also received.

I hope this makes sense and that you are able to solve this problem and
let
me know what formula to use to achieve this.

THank you,
Mike

"T. Valko" wrote:

I'm confused about the received/billed stuff.

It sounds like you want a SUM and not a COUNT. Can you post several
examples
of the received/billed criteria and what result is expected?

Biff

"WiFiMike2006" wrote in message
...
I don't know who Driller is, but I'm betting he knows a lot more than I
do
about excel. I know this one is seriously complicated. I'm just hoping
there
is some way to do it.

Here's is the actual formula as I'm trying it now, with correct ranges
and
sheet references:

=SUMPRODUCT((POs!B2:B2000="century
city")*(POs!H2:H2000={"*server*","*370*","*470*"}) *(POs!K2:K2000+POs!L2:L2000))

I also tried:

=SUMPRODUCT(--(POs!B2:B2000="century
city"),--(ISNUMBER(MATCH(POs!H2:H2000,{"*server*","*370*"," *470*"},0))),--(POs!K2:K2000<""),--(POs!L2:L2000<""))

Unfortunately, neither one is working.

Maybe if I describe what I need the formula to do in steps it will
make it
easier to figure out what I need:

1. filter results where B2:B2000 matches "century city".
2. narrow those results by H2:H2000 *contains* the text "server",
"370",
or
"470".
3. out of those items, add up only the quantity billed in L2:L2000
that is
also shown as received in K2:K2000. This last step may require some
<=/=,
IF(AND type of stuff to only count the quantity in column L that also
appears
in column K. But I really have no idea.

If it would help, I could email you a sample of the spreadsheet. Just
let
me
know where to email it.

I know this is a serious brain cruncher. But the last time I had to go
through and add up all this data manually because I couldn't figure
out a
formula to do it, it took me over 2 weeks, on top of all the other
work I
have to do. There are over 100 items on over 30 projects that I have
to
count
billed-and-also-received items for on the PO report. So if it makes
you
feel
any better about solving this one, you'll allow me to spend less hours
at
work, thereby giving me more time with my kids.

Thank you,

Mike


"T. Valko" wrote:

Is this "Driller" ?

<G

Biff

"WiFiMike2006" wrote in
message
...
Cool. THANK YOU!

Just one more question, can I use a wildcard to search for text
*contained*
in the item list in this formula? For example:

=SUMPRODUCT((SheetA!A2:A10="one")*(SheetA!B2:B10={ "*server*","*370*","*470*"})*(SheetA!C2:C10+SheetA !D2:D10))

...because, in reality, the items column is a bunch of item
descriptions
with stuff like "Dell Precision 370" or "Dell server quote" in
them.
They
aren't as simple as just a one word description.

Also, I need to test this formula you gave still. I'm not sure, but
it
looks
like it will add the received and billed quantities for each item,
instead
of
adding quantity of items billed that were also received. For
example,
if
received = 0, and billed = 2, i wouldn't want it to count 2, I
would
want
it
to count 0. If received = 2 and billed = 1, it should count 1. If
received =
4 and billed = 2, it should count 2. Basically, it should count
quantity
billed that are also received. Understand?

Thanks,
Mike

"RagDyer" wrote:

Try this:

=SUMPRODUCT((SheetA!A2:A10="one")*(SheetA!B2:B10={ "server",370,470})*(SheetA!C2:C10+SheetA!D2:D1 0))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may
benefit !
---------------------------------------------------------------------------
"WiFiMike2006" wrote in
message
...
i need to count data from sheet "A" based on several criteria and
enter
it
on
sheet "B".

Sheet A:

a b c
d
1 site item received
billed
2 one server 2
1
3 one 370 1
2
4 one 470 1
1
5 two server 1
1

For this example, i need to count only the total quantity of
items
both
billed and received for any occurrence of either "server",
"370", or
"470"
listed in sheet A where the "site" is listed as "one".

For this example, the formula's result would be 3, since only 3
were
billed
and received for site one.

PLEASE HELP! Counting all this data manually for over 100 items
and
over
30
projects is taking me WEEKS to complete.

Thank you!

Mike

counting occurrences in another sheet based on several criteri
 

Hmmm....

After revisiting this I see that formula doesn't work properly.....it finds
the difference between reveived/billed.

I think the best way to do this would be to use a helper column:

____K______L______M
1___rec'd___billed______
2___2______2_______2 =MIN(K2:L2)
3___3______1_______1 =MIN(K3:L3)
4___0______2_______0 =MIN(K4:L4)
5___2______5_______2 =MIN(K5:L5)
6___5______0_______0 =MIN(K6:L6)

Then the formula becomes:

=SUMPRODUCT((POs!B2:B2000="century city")*
(ISNUMBER(SEARCH({"server",370,470},POs!H2:H2000)) )*POs!M2:M2000)

Much simpler!

Biff

"T. Valko" wrote in message
...
Disregard that last formula!

Try this one entered as an array using the key combination of
CTRL,SHIFT,ENTER (not just ENTER):

=SUMPRODUCT((A2:A6="century city")*
(ISNUMBER(SEARCH({"server","370","470"},B2:B6)))*
(C2:C60)*(D2:D60)*
IF(C2:C6=D2:D6,C2:C6,ABS(C2:C6-D2:D6)))

Biff

"T. Valko" wrote in message
...
with the formula applied, the result for the above example would be 7.

How about 5?

=SUMPRODUCT((A2:A6="century
city")*(ISNUMBER(SEARCH({"server","370","470"},B2: B6)))*(C2:C60)*(D2:D60)*ABS(C2:C6-D2:D6))

Biff

"WiFiMike2006" wrote in message
...
Indeed, i do need a sum of the quantity billed and also received.

Let's assume that these results are all filtered by site "century city",
and
filtered by text wildcards "server", "370" and "470" in the item
description
column already, and we're just looking at the "qty. received" and "qty.
billed" columns.


____K______L____________________________
1___rec'd___billed____sum returned by formula
2___2______2_______2 (billed = received, qty. of billed summed)
3___3______1_______1 (billed < received, qty. of billed summed)
4___0______2_______0 (billed or received = 0, 0 sum)
5___2______5_______2 (billed received by 3, qty. of received summed)
6___5______0_______2 (billed or received = 0, 0 sum)

with the formula applied, the result for the above example would be 7.
this
would be the total amount of items for the given site with any of the
specified text wildcards contained in the item description that were
billed,
and also received.

I hope this makes sense and that you are able to solve this problem and
let
me know what formula to use to achieve this.

THank you,
Mike

"T. Valko" wrote:

I'm confused about the received/billed stuff.

It sounds like you want a SUM and not a COUNT. Can you post several
examples
of the received/billed criteria and what result is expected?

Biff

"WiFiMike2006" wrote in
message
...
I don't know who Driller is, but I'm betting he knows a lot more than
I do
about excel. I know this one is seriously complicated. I'm just
hoping
there
is some way to do it.

Here's is the actual formula as I'm trying it now, with correct
ranges and
sheet references:

=SUMPRODUCT((POs!B2:B2000="century
city")*(POs!H2:H2000={"*server*","*370*","*470*"}) *(POs!K2:K2000+POs!L2:L2000))

I also tried:

=SUMPRODUCT(--(POs!B2:B2000="century
city"),--(ISNUMBER(MATCH(POs!H2:H2000,{"*server*","*370*"," *470*"},0))),--(POs!K2:K2000<""),--(POs!L2:L2000<""))

Unfortunately, neither one is working.

Maybe if I describe what I need the formula to do in steps it will
make it
easier to figure out what I need:

1. filter results where B2:B2000 matches "century city".
2. narrow those results by H2:H2000 *contains* the text "server",
"370",
or
"470".
3. out of those items, add up only the quantity billed in L2:L2000
that is
also shown as received in K2:K2000. This last step may require some
<=/=,
IF(AND type of stuff to only count the quantity in column L that also
appears
in column K. But I really have no idea.

If it would help, I could email you a sample of the spreadsheet. Just
let
me
know where to email it.

I know this is a serious brain cruncher. But the last time I had to
go
through and add up all this data manually because I couldn't figure
out a
formula to do it, it took me over 2 weeks, on top of all the other
work I
have to do. There are over 100 items on over 30 projects that I have
to
count
billed-and-also-received items for on the PO report. So if it makes
you
feel
any better about solving this one, you'll allow me to spend less
hours at
work, thereby giving me more time with my kids.

Thank you,

Mike


"T. Valko" wrote:

Is this "Driller" ?

<G

Biff

"WiFiMike2006" wrote in
message
...
Cool. THANK YOU!

Just one more question, can I use a wildcard to search for text
*contained*
in the item list in this formula? For example:

=SUMPRODUCT((SheetA!A2:A10="one")*(SheetA!B2:B10={ "*server*","*370*","*470*"})*(SheetA!C2:C10+SheetA !D2:D10))

...because, in reality, the items column is a bunch of item
descriptions
with stuff like "Dell Precision 370" or "Dell server quote" in
them.
They
aren't as simple as just a one word description.

Also, I need to test this formula you gave still. I'm not sure,
but it
looks
like it will add the received and billed quantities for each item,
instead
of
adding quantity of items billed that were also received. For
example,
if
received = 0, and billed = 2, i wouldn't want it to count 2, I
would
want
it
to count 0. If received = 2 and billed = 1, it should count 1. If
received =
4 and billed = 2, it should count 2. Basically, it should count
quantity
billed that are also received. Understand?

Thanks,
Mike

"RagDyer" wrote:

Try this:

=SUMPRODUCT((SheetA!A2:A10="one")*(SheetA!B2:B10={ "server",370,470})*(SheetA!C2:C10+SheetA!D2:D1 0))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may
benefit !
---------------------------------------------------------------------------
"WiFiMike2006" wrote in
message
...
i need to count data from sheet "A" based on several criteria
and
enter
it
on
sheet "B".

Sheet A:

a b c
d
1 site item received
billed
2 one server 2
1
3 one 370 1
2
4 one 470 1
1
5 two server 1
1

For this example, i need to count only the total quantity of
items
both
billed and received for any occurrence of either "server",
"370", or
"470"
listed in sheet A where the "site" is listed as "one".

For this example, the formula's result would be 3, since only 3
were
billed
and received for site one.

PLEASE HELP! Counting all this data manually for over 100 items
and
over
30
projects is taking me WEEKS to complete.

Thank you!

Mike

counting occurrences in another sheet based on several criteri
 

Oops. Yeah it should be 5. It was getting late and I revised the table I
entered after I tried to post the reply and it didn't appear to go through. I
had copied all the text to the clipboard, so i composed the reply again and
pasted everyting in, but the table didn't paste right so I redid it. I guess
i didn't think to check the sum I entered in the text after it though.

Thank you!

"T. Valko" wrote:

with the formula applied, the result for the above example would be 7.

How about 5?

=SUMPRODUCT((A2:A6="century
city")*(ISNUMBER(SEARCH({"server","370","470"},B2: B6)))*(C2:C60)*(D2:D60)*ABS(C2:C6-D2:D6))

Biff

"WiFiMike2006" wrote in message
...
Indeed, i do need a sum of the quantity billed and also received.

Let's assume that these results are all filtered by site "century city",
and
filtered by text wildcards "server", "370" and "470" in the item
description
column already, and we're just looking at the "qty. received" and "qty.
billed" columns.


____K______L____________________________
1___rec'd___billed____sum returned by formula
2___2______2_______2 (billed = received, qty. of billed summed)
3___3______1_______1 (billed < received, qty. of billed summed)
4___0______2_______0 (billed or received = 0, 0 sum)
5___2______5_______2 (billed received by 3, qty. of received summed)
6___5______0_______2 (billed or received = 0, 0 sum)

with the formula applied, the result for the above example would be 7.
this
would be the total amount of items for the given site with any of the
specified text wildcards contained in the item description that were
billed,
and also received.

I hope this makes sense and that you are able to solve this problem and
let
me know what formula to use to achieve this.

THank you,
Mike

"T. Valko" wrote:

I'm confused about the received/billed stuff.

It sounds like you want a SUM and not a COUNT. Can you post several
examples
of the received/billed criteria and what result is expected?

Biff

"WiFiMike2006" wrote in message
...
I don't know who Driller is, but I'm betting he knows a lot more than I
do
about excel. I know this one is seriously complicated. I'm just hoping
there
is some way to do it.

Here's is the actual formula as I'm trying it now, with correct ranges
and
sheet references:

=SUMPRODUCT((POs!B2:B2000="century
city")*(POs!H2:H2000={"*server*","*370*","*470*"}) *(POs!K2:K2000+POs!L2:L2000))

I also tried:

=SUMPRODUCT(--(POs!B2:B2000="century
city"),--(ISNUMBER(MATCH(POs!H2:H2000,{"*server*","*370*"," *470*"},0))),--(POs!K2:K2000<""),--(POs!L2:L2000<""))

Unfortunately, neither one is working.

Maybe if I describe what I need the formula to do in steps it will make
it
easier to figure out what I need:

1. filter results where B2:B2000 matches "century city".
2. narrow those results by H2:H2000 *contains* the text "server",
"370",
or
"470".
3. out of those items, add up only the quantity billed in L2:L2000 that
is
also shown as received in K2:K2000. This last step may require some
<=/=,
IF(AND type of stuff to only count the quantity in column L that also
appears
in column K. But I really have no idea.

If it would help, I could email you a sample of the spreadsheet. Just
let
me
know where to email it.

I know this is a serious brain cruncher. But the last time I had to go
through and add up all this data manually because I couldn't figure out
a
formula to do it, it took me over 2 weeks, on top of all the other work
I
have to do. There are over 100 items on over 30 projects that I have to
count
billed-and-also-received items for on the PO report. So if it makes you
feel
any better about solving this one, you'll allow me to spend less hours
at
work, thereby giving me more time with my kids.

Thank you,

Mike


"T. Valko" wrote:

Is this "Driller" ?

<G

Biff

"WiFiMike2006" wrote in
message
...
Cool. THANK YOU!

Just one more question, can I use a wildcard to search for text
*contained*
in the item list in this formula? For example:

=SUMPRODUCT((SheetA!A2:A10="one")*(SheetA!B2:B10={ "*server*","*370*","*470*"})*(SheetA!C2:C10+SheetA !D2:D10))

...because, in reality, the items column is a bunch of item
descriptions
with stuff like "Dell Precision 370" or "Dell server quote" in them.
They
aren't as simple as just a one word description.

Also, I need to test this formula you gave still. I'm not sure, but
it
looks
like it will add the received and billed quantities for each item,
instead
of
adding quantity of items billed that were also received. For
example,
if
received = 0, and billed = 2, i wouldn't want it to count 2, I would
want
it
to count 0. If received = 2 and billed = 1, it should count 1. If
received =
4 and billed = 2, it should count 2. Basically, it should count
quantity
billed that are also received. Understand?

Thanks,
Mike

"RagDyer" wrote:

Try this:

=SUMPRODUCT((SheetA!A2:A10="one")*(SheetA!B2:B10={ "server",370,470})*(SheetA!C2:C10+SheetA!D2:D1 0))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may
benefit !
---------------------------------------------------------------------------
"WiFiMike2006" wrote in
message
...
i need to count data from sheet "A" based on several criteria and
enter
it
on
sheet "B".

Sheet A:

a b c
d
1 site item received
billed
2 one server 2
1
3 one 370 1
2
4 one 470 1
1
5 two server 1
1

For this example, i need to count only the total quantity of
items
both
billed and received for any occurrence of either "server", "370",
or
"470"
listed in sheet A where the "site" is listed as "one".

For this example, the formula's result would be 3, since only 3
were
billed
and received for site one.

PLEASE HELP! Counting all this data manually for over 100 items
and
over
30
projects is taking me WEEKS to complete.

Thank you!

Mike

counting occurrences in another sheet based on several criteri
 

If you absolutely have to have a single formula to do this:

=SUMPRODUCT((B2:B10="century city")*
(ISNUMBER(SEARCH({"server",370,470},H2:H10)))*
SUBTOTAL(5,OFFSET(K2:L10,ROW(K2:L10)-2,,1,2)))

However, I think I would use the helper column with the MIN formula. The
above formula is not real efficient, especially on large ranges like you
have.

Biff

"WiFiMike2006" wrote in message
...
Oops. Yeah it should be 5. It was getting late and I revised the table I
entered after I tried to post the reply and it didn't appear to go
through. I
had copied all the text to the clipboard, so i composed the reply again
and
pasted everyting in, but the table didn't paste right so I redid it. I
guess
i didn't think to check the sum I entered in the text after it though.

Thank you!

"T. Valko" wrote:

with the formula applied, the result for the above example would be 7.

How about 5?

=SUMPRODUCT((A2:A6="century
city")*(ISNUMBER(SEARCH({"server","370","470"},B2: B6)))*(C2:C60)*(D2:D60)*ABS(C2:C6-D2:D6))

Biff

"WiFiMike2006" wrote in message
...
Indeed, i do need a sum of the quantity billed and also received.

Let's assume that these results are all filtered by site "century
city",
and
filtered by text wildcards "server", "370" and "470" in the item
description
column already, and we're just looking at the "qty. received" and "qty.
billed" columns.


____K______L____________________________
1___rec'd___billed____sum returned by formula
2___2______2_______2 (billed = received, qty. of billed summed)
3___3______1_______1 (billed < received, qty. of billed summed)
4___0______2_______0 (billed or received = 0, 0 sum)
5___2______5_______2 (billed received by 3, qty. of received summed)
6___5______0_______2 (billed or received = 0, 0 sum)

with the formula applied, the result for the above example would be 7.
this
would be the total amount of items for the given site with any of the
specified text wildcards contained in the item description that were
billed,
and also received.

I hope this makes sense and that you are able to solve this problem and
let
me know what formula to use to achieve this.

THank you,
Mike

"T. Valko" wrote:

I'm confused about the received/billed stuff.

It sounds like you want a SUM and not a COUNT. Can you post several
examples
of the received/billed criteria and what result is expected?

Biff

"WiFiMike2006" wrote in
message
...
I don't know who Driller is, but I'm betting he knows a lot more than
I
do
about excel. I know this one is seriously complicated. I'm just
hoping
there
is some way to do it.

Here's is the actual formula as I'm trying it now, with correct
ranges
and
sheet references:

=SUMPRODUCT((POs!B2:B2000="century
city")*(POs!H2:H2000={"*server*","*370*","*470*"}) *(POs!K2:K2000+POs!L2:L2000))

I also tried:

=SUMPRODUCT(--(POs!B2:B2000="century
city"),--(ISNUMBER(MATCH(POs!H2:H2000,{"*server*","*370*"," *470*"},0))),--(POs!K2:K2000<""),--(POs!L2:L2000<""))

Unfortunately, neither one is working.

Maybe if I describe what I need the formula to do in steps it will
make
it
easier to figure out what I need:

1. filter results where B2:B2000 matches "century city".
2. narrow those results by H2:H2000 *contains* the text "server",
"370",
or
"470".
3. out of those items, add up only the quantity billed in L2:L2000
that
is
also shown as received in K2:K2000. This last step may require some
<=/=,
IF(AND type of stuff to only count the quantity in column L that
also
appears
in column K. But I really have no idea.

If it would help, I could email you a sample of the spreadsheet.
Just
let
me
know where to email it.

I know this is a serious brain cruncher. But the last time I had to
go
through and add up all this data manually because I couldn't figure
out
a
formula to do it, it took me over 2 weeks, on top of all the other
work
I
have to do. There are over 100 items on over 30 projects that I have
to
count
billed-and-also-received items for on the PO report. So if it makes
you
feel
any better about solving this one, you'll allow me to spend less
hours
at
work, thereby giving me more time with my kids.

Thank you,

Mike


"T. Valko" wrote:

Is this "Driller" ?

<G

Biff

"WiFiMike2006" wrote in
message
...
Cool. THANK YOU!

Just one more question, can I use a wildcard to search for text
*contained*
in the item list in this formula? For example:

=SUMPRODUCT((SheetA!A2:A10="one")*(SheetA!B2:B10={ "*server*","*370*","*470*"})*(SheetA!C2:C10+SheetA !D2:D10))

...because, in reality, the items column is a bunch of item
descriptions
with stuff like "Dell Precision 370" or "Dell server quote" in
them.
They
aren't as simple as just a one word description.

Also, I need to test this formula you gave still. I'm not sure,
but
it
looks
like it will add the received and billed quantities for each
item,
instead
of
adding quantity of items billed that were also received. For
example,
if
received = 0, and billed = 2, i wouldn't want it to count 2, I
would
want
it
to count 0. If received = 2 and billed = 1, it should count 1.
If
received =
4 and billed = 2, it should count 2. Basically, it should count
quantity
billed that are also received. Understand?

Thanks,
Mike

"RagDyer" wrote:

Try this:

=SUMPRODUCT((SheetA!A2:A10="one")*(SheetA!B2:B10={ "server",370,470})*(SheetA!C2:C10+SheetA!D2:D1 0))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may
benefit !
---------------------------------------------------------------------------
"WiFiMike2006" wrote in
message
...
i need to count data from sheet "A" based on several criteria
and
enter
it
on
sheet "B".

Sheet A:

a b c
d
1 site item received
billed
2 one server 2
1
3 one 370 1
2
4 one 470 1
1
5 two server 1
1

For this example, i need to count only the total quantity of
items
both
billed and received for any occurrence of either "server",
"370",
or
"470"
listed in sheet A where the "site" is listed as "one".

For this example, the formula's result would be 3, since only
3
were
billed
and received for site one.

PLEASE HELP! Counting all this data manually for over 100
items
and
over
30
projects is taking me WEEKS to complete.

Thank you!

Mike

counting occurrences in another sheet based on several criteri
 

Ok. Now the only problem is that it seems to sum up some items more than once
because the ISNUMBER(SEARCH sometimes finds more than one instance of
"server",370, or 470 in the same item description. In other words, if one of
the item's descriptions in column H is "Dell preceision 370 server", the MIN
of items billed vs. received is summed twice by the formula because the
description has "server" and "370" in it.

Is there any way to change the formula so that it will only add each row's
MIN qty. one time regardless of how many instances of the search criteria
appear in the item description for that item?

Thank you for all the help. I'm truly grateful and impressed with the
results thus far. I just hope that it is possible to iron out this one last
kink in the formula. because other than that, it appears to be working
perfectly.

Mike

"T. Valko" wrote:

Hmmm....

After revisiting this I see that formula doesn't work properly.....it finds
the difference between reveived/billed.

I think the best way to do this would be to use a helper column:

____K______L______M
1___rec'd___billed______
2___2______2_______2 =MIN(K2:L2)
3___3______1_______1 =MIN(K3:L3)
4___0______2_______0 =MIN(K4:L4)
5___2______5_______2 =MIN(K5:L5)
6___5______0_______0 =MIN(K6:L6)

Then the formula becomes:

=SUMPRODUCT((POs!B2:B2000="century city")*
(ISNUMBER(SEARCH({"server",370,470},POs!H2:H2000)) )*POs!M2:M2000)

Much simpler!

Biff

"T. Valko" wrote in message
...
Disregard that last formula!

Try this one entered as an array using the key combination of
CTRL,SHIFT,ENTER (not just ENTER):

=SUMPRODUCT((A2:A6="century city")*
(ISNUMBER(SEARCH({"server","370","470"},B2:B6)))*
(C2:C60)*(D2:D60)*
IF(C2:C6=D2:D6,C2:C6,ABS(C2:C6-D2:D6)))

Biff

"T. Valko" wrote in message
...
with the formula applied, the result for the above example would be 7.

How about 5?

=SUMPRODUCT((A2:A6="century
city")*(ISNUMBER(SEARCH({"server","370","470"},B2: B6)))*(C2:C60)*(D2:D60)*ABS(C2:C6-D2:D6))

Biff

"WiFiMike2006" wrote in message
...
Indeed, i do need a sum of the quantity billed and also received.

Let's assume that these results are all filtered by site "century city",
and
filtered by text wildcards "server", "370" and "470" in the item
description
column already, and we're just looking at the "qty. received" and "qty.
billed" columns.


____K______L____________________________
1___rec'd___billed____sum returned by formula
2___2______2_______2 (billed = received, qty. of billed summed)
3___3______1_______1 (billed < received, qty. of billed summed)
4___0______2_______0 (billed or received = 0, 0 sum)
5___2______5_______2 (billed received by 3, qty. of received summed)
6___5______0_______2 (billed or received = 0, 0 sum)

with the formula applied, the result for the above example would be 7.
this
would be the total amount of items for the given site with any of the
specified text wildcards contained in the item description that were
billed,
and also received.

I hope this makes sense and that you are able to solve this problem and
let
me know what formula to use to achieve this.

THank you,
Mike

"T. Valko" wrote:

I'm confused about the received/billed stuff.

It sounds like you want a SUM and not a COUNT. Can you post several
examples
of the received/billed criteria and what result is expected?

Biff

"WiFiMike2006" wrote in
message
...
I don't know who Driller is, but I'm betting he knows a lot more than
I do
about excel. I know this one is seriously complicated. I'm just
hoping
there
is some way to do it.

Here's is the actual formula as I'm trying it now, with correct
ranges and
sheet references:

=SUMPRODUCT((POs!B2:B2000="century
city")*(POs!H2:H2000={"*server*","*370*","*470*"}) *(POs!K2:K2000+POs!L2:L2000))

I also tried:

=SUMPRODUCT(--(POs!B2:B2000="century
city"),--(ISNUMBER(MATCH(POs!H2:H2000,{"*server*","*370*"," *470*"},0))),--(POs!K2:K2000<""),--(POs!L2:L2000<""))

Unfortunately, neither one is working.

Maybe if I describe what I need the formula to do in steps it will
make it
easier to figure out what I need:

1. filter results where B2:B2000 matches "century city".
2. narrow those results by H2:H2000 *contains* the text "server",
"370",
or
"470".
3. out of those items, add up only the quantity billed in L2:L2000
that is
also shown as received in K2:K2000. This last step may require some
<=/=,
IF(AND type of stuff to only count the quantity in column L that also
appears
in column K. But I really have no idea.

If it would help, I could email you a sample of the spreadsheet. Just
let
me
know where to email it.

I know this is a serious brain cruncher. But the last time I had to
go
through and add up all this data manually because I couldn't figure
out a
formula to do it, it took me over 2 weeks, on top of all the other
work I
have to do. There are over 100 items on over 30 projects that I have
to
count
billed-and-also-received items for on the PO report. So if it makes
you
feel
any better about solving this one, you'll allow me to spend less
hours at
work, thereby giving me more time with my kids.

Thank you,

Mike


"T. Valko" wrote:

Is this "Driller" ?

<G

Biff

"WiFiMike2006" wrote in
message
...
Cool. THANK YOU!

Just one more question, can I use a wildcard to search for text
*contained*
in the item list in this formula? For example:

=SUMPRODUCT((SheetA!A2:A10="one")*(SheetA!B2:B10={ "*server*","*370*","*470*"})*(SheetA!C2:C10+SheetA !D2:D10))

...because, in reality, the items column is a bunch of item
descriptions
with stuff like "Dell Precision 370" or "Dell server quote" in
them.
They
aren't as simple as just a one word description.

Also, I need to test this formula you gave still. I'm not sure,
but it
looks
like it will add the received and billed quantities for each item,
instead
of
adding quantity of items billed that were also received. For
example,
if
received = 0, and billed = 2, i wouldn't want it to count 2, I
would
want
it
to count 0. If received = 2 and billed = 1, it should count 1. If
received =
4 and billed = 2, it should count 2. Basically, it should count
quantity
billed that are also received. Understand?

Thanks,
Mike

"RagDyer" wrote:

Try this:

=SUMPRODUCT((SheetA!A2:A10="one")*(SheetA!B2:B10={ "server",370,470})*(SheetA!C2:C10+SheetA!D2:D1 0))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may
benefit !
---------------------------------------------------------------------------
"WiFiMike2006" wrote in
message
...
i need to count data from sheet "A" based on several criteria
and
enter
it
on
sheet "B".

Sheet A:

a b c
d
1 site item received
billed
2 one server 2
1
3 one 370 1
2
4 one 470 1
1
5 two server 1
1

For this example, i need to count only the total quantity of
items
both
billed and received for any occurrence of either "server",
"370", or
"470"
listed in sheet A where the "site" is listed as "one".

For this example, the formula's result would be 3, since only 3
were
billed
and received for site one.

PLEASE HELP! Counting all this data manually for over 100 items
and
over
30
projects is taking me WEEKS to complete.

Thank you!

Mike

counting occurrences in another sheet based on several criteri
 

Can you send me a small sample file? I don't need 1000's of rows of data!
Maybe 100 rows of representative data. If you can do that I'm at:

xl can help at comcast period net

Remove "can" and change the obvious.

Biff

"WiFiMike2006" wrote in message
...
Ok. Now the only problem is that it seems to sum up some items more than
once
because the ISNUMBER(SEARCH sometimes finds more than one instance of
"server",370, or 470 in the same item description. In other words, if one
of
the item's descriptions in column H is "Dell preceision 370 server", the
MIN
of items billed vs. received is summed twice by the formula because the
description has "server" and "370" in it.

Is there any way to change the formula so that it will only add each row's
MIN qty. one time regardless of how many instances of the search criteria
appear in the item description for that item?

Thank you for all the help. I'm truly grateful and impressed with the
results thus far. I just hope that it is possible to iron out this one
last
kink in the formula. because other than that, it appears to be working
perfectly.

Mike

"T. Valko" wrote:

Hmmm....

After revisiting this I see that formula doesn't work properly.....it
finds
the difference between reveived/billed.

I think the best way to do this would be to use a helper column:

____K______L______M
1___rec'd___billed______
2___2______2_______2 =MIN(K2:L2)
3___3______1_______1 =MIN(K3:L3)
4___0______2_______0 =MIN(K4:L4)
5___2______5_______2 =MIN(K5:L5)
6___5______0_______0 =MIN(K6:L6)

Then the formula becomes:

=SUMPRODUCT((POs!B2:B2000="century city")*
(ISNUMBER(SEARCH({"server",370,470},POs!H2:H2000)) )*POs!M2:M2000)

Much simpler!

Biff

"T. Valko" wrote in message
...
Disregard that last formula!

Try this one entered as an array using the key combination of
CTRL,SHIFT,ENTER (not just ENTER):

=SUMPRODUCT((A2:A6="century city")*
(ISNUMBER(SEARCH({"server","370","470"},B2:B6)))*
(C2:C60)*(D2:D60)*
IF(C2:C6=D2:D6,C2:C6,ABS(C2:C6-D2:D6)))

Biff

"T. Valko" wrote in message
...
with the formula applied, the result for the above example would be
7.

How about 5?

=SUMPRODUCT((A2:A6="century
city")*(ISNUMBER(SEARCH({"server","370","470"},B2: B6)))*(C2:C60)*(D2:D60)*ABS(C2:C6-D2:D6))

Biff

"WiFiMike2006" wrote in
message
...
Indeed, i do need a sum of the quantity billed and also received.

Let's assume that these results are all filtered by site "century
city",
and
filtered by text wildcards "server", "370" and "470" in the item
description
column already, and we're just looking at the "qty. received" and
"qty.
billed" columns.


____K______L____________________________
1___rec'd___billed____sum returned by formula
2___2______2_______2 (billed = received, qty. of billed summed)
3___3______1_______1 (billed < received, qty. of billed summed)
4___0______2_______0 (billed or received = 0, 0 sum)
5___2______5_______2 (billed received by 3, qty. of received
summed)
6___5______0_______2 (billed or received = 0, 0 sum)

with the formula applied, the result for the above example would be
7.
this
would be the total amount of items for the given site with any of the
specified text wildcards contained in the item description that were
billed,
and also received.

I hope this makes sense and that you are able to solve this problem
and
let
me know what formula to use to achieve this.

THank you,
Mike

"T. Valko" wrote:

I'm confused about the received/billed stuff.

It sounds like you want a SUM and not a COUNT. Can you post several
examples
of the received/billed criteria and what result is expected?

Biff

"WiFiMike2006" wrote in
message
...
I don't know who Driller is, but I'm betting he knows a lot more
than
I do
about excel. I know this one is seriously complicated. I'm just
hoping
there
is some way to do it.

Here's is the actual formula as I'm trying it now, with correct
ranges and
sheet references:

=SUMPRODUCT((POs!B2:B2000="century
city")*(POs!H2:H2000={"*server*","*370*","*470*"}) *(POs!K2:K2000+POs!L2:L2000))

I also tried:

=SUMPRODUCT(--(POs!B2:B2000="century
city"),--(ISNUMBER(MATCH(POs!H2:H2000,{"*server*","*370*"," *470*"},0))),--(POs!K2:K2000<""),--(POs!L2:L2000<""))

Unfortunately, neither one is working.

Maybe if I describe what I need the formula to do in steps it will
make it
easier to figure out what I need:

1. filter results where B2:B2000 matches "century city".
2. narrow those results by H2:H2000 *contains* the text "server",
"370",
or
"470".
3. out of those items, add up only the quantity billed in L2:L2000
that is
also shown as received in K2:K2000. This last step may require
some
<=/=,
IF(AND type of stuff to only count the quantity in column L that
also
appears
in column K. But I really have no idea.

If it would help, I could email you a sample of the spreadsheet.
Just
let
me
know where to email it.

I know this is a serious brain cruncher. But the last time I had
to
go
through and add up all this data manually because I couldn't
figure
out a
formula to do it, it took me over 2 weeks, on top of all the other
work I
have to do. There are over 100 items on over 30 projects that I
have
to
count
billed-and-also-received items for on the PO report. So if it
makes
you
feel
any better about solving this one, you'll allow me to spend less
hours at
work, thereby giving me more time with my kids.

Thank you,

Mike


"T. Valko" wrote:

Is this "Driller" ?

<G

Biff

"WiFiMike2006" wrote in
message
...
Cool. THANK YOU!

Just one more question, can I use a wildcard to search for text
*contained*
in the item list in this formula? For example:

=SUMPRODUCT((SheetA!A2:A10="one")*(SheetA!B2:B10={ "*server*","*370*","*470*"})*(SheetA!C2:C10+SheetA !D2:D10))

...because, in reality, the items column is a bunch of item
descriptions
with stuff like "Dell Precision 370" or "Dell server quote" in
them.
They
aren't as simple as just a one word description.

Also, I need to test this formula you gave still. I'm not sure,
but it
looks
like it will add the received and billed quantities for each
item,
instead
of
adding quantity of items billed that were also received. For
example,
if
received = 0, and billed = 2, i wouldn't want it to count 2, I
would
want
it
to count 0. If received = 2 and billed = 1, it should count 1.
If
received =
4 and billed = 2, it should count 2. Basically, it should count
quantity
billed that are also received. Understand?

Thanks,
Mike

"RagDyer" wrote:

Try this:

=SUMPRODUCT((SheetA!A2:A10="one")*(SheetA!B2:B10={ "server",370,470})*(SheetA!C2:C10+SheetA!D2:D1 0))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all
may
benefit !
---------------------------------------------------------------------------
"WiFiMike2006" wrote
in
message
...
i need to count data from sheet "A" based on several criteria
and
enter
it
on
sheet "B".

Sheet A:

a b c
d
1 site item received
billed
2 one server 2
1
3 one 370 1
2
4 one 470 1
1
5 two server 1
1

For this example, i need to count only the total quantity of
items
both
billed and received for any occurrence of either "server",
"370", or
"470"
listed in sheet A where the "site" is listed as "one".

For this example, the formula's result would be 3, since
only 3
were
billed
and received for site one.

PLEASE HELP! Counting all this data manually for over 100
items
and
over
30
projects is taking me WEEKS to complete.

Thank you!

Mike