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