counting occurrences in another sheet based on several criteria

[WiFiMike2006]

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

 

[WiFiMike2006]

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

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

 

[T. Valko]

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<>""),--(D25<>""))

Biff

 

[RagDyer]

Try this:

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

 

[WiFiMike2006]

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!D210))

....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

 

[T. Valko]

Is this "Driller" ?

<G>

Biff

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!D210))

...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

 

[WiFiMike2006]

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

 

[WiFiMike2006]

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]

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]

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]

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:C6>0)*(D26>0)*ABS(C2:C6-D26))

Biff

 

[T. Valko]

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:C6>0)*(D26>0)*
IF(C2:C6=D26,C2:C6,ABS(C2:C6-D26)))

Biff

 

[T. Valko]

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]

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

 

[T. Valko]

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