Is this even possible..........????

  • Thread starter Thread starter Babylon 5 Fan
  • Start date Start date
B

Babylon 5 Fan

Hi all

It's been a long time since contacting this newsgroup. Thanks to those
who helped my in the past......(only a couple of times....don't want to
abuse your kindness)

This time the challenge is way beyond my sphere of knowledge.......don't
even know if what I'm asking is possible ???

The subject sheet is relatively simple............it is nine columns wide
and hundreds of lines long. mmmm how do I go on from here?? How about
just writing it in semi English and see if we can get an Excel formula that
may work.....



Nine columns wide, hundreds of lines long..........from this sheet the
challenge is to extract information something like this......

Count the number of lines if the line meets the following criteria.......

Column 1 = "1350" and Column 2 = either "APPROVED" or "REQUAL PENDING" and
Column 5 = "E" and Column 6 = "MM"



The rest of my sheet has been completed with other formula to give me
information........this is the final step to make the information useful.

Thanks in advance

Andy
 
:
....
Count the number of lines if the line meets the following criteria.......
Column 1 = "1350" and Column 2 = either "APPROVED" or
"REQUAL PENDING" and
Column 5 = "E" and Column 6 = "MM"
Click to expand...

One way

Try something like:

=SUMPRODUCT((A2:A100=1350)*(ISNUMBER(FIND("APPROVED",B2:B100))+ISNUMBER(FIND
("REQUAL PENDING",B2:B100))*(E2:E100="E")*(F2:F100="MM")))

Adapt the ranges to suit, but note that entire col references (A:A, B:B,
etc) are not accepted in SUMPRODUCT
 
Bit shorter

=SUMPRODUCT((A2:A100=1350)*(ISNUMBER(FIND({"APPROVED","REQUAL
PENDING"},B2:B100))*(E2:E100="E")*(F2:F100="MM")))

and if not important to be upper-case then

=SUMPRODUCT((A2:A100=1350)*(ISNUMBER(SEARCH({"APPROVED","REQUAL
PENDING"},B2:B100))*(E2:E100="E")*(F2:F100="MM")))

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Thank you both for your suggestions.

I will take this in tomorrow and give it a try.

Appreciate the input

Andy



Bob Phillips said:
Bit shorter

=SUMPRODUCT((A2:A100=1350)*(ISNUMBER(FIND({"APPROVED","REQUAL
PENDING"},B2:B100))*(E2:E100="E")*(F2:F100="MM")))

and if not important to be upper-case then

=SUMPRODUCT((A2:A100=1350)*(ISNUMBER(SEARCH({"APPROVED","REQUAL
PENDING"},B2:B100))*(E2:E100="E")*(F2:F100="MM")))

--

HTH

RP
(remove nothere from the email address if mailing direct)
Click to expand...
 
BRILLIANT !!!

Completed the remainder of the spreadsheet today and it works like a charm.

My sincere thanks to you both for your input.

Andy
 
BRILLIANT !!!

Completed the spreadsheet today and it works like a charm.

My sincere thanks to you both for your assistance.

Andy
 
Back
Top