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