Extract and sum data

[Summer]

I have the following table:

Column A - Date. The date is only listed for line number 1, but
applies to all lines until the next date.
Column B - Line (machine) number
Column C - Pounds of scrap
Column D - Reason code for the scrap. This is always from a short list
of specific words.

The following headers begin on line 5.

Date | Line | Scrap Lbs | Scrap Reason
1/1 1 23 Equipment
2 18 Power failure
3 0
4 20 Graphics

1/2 1 0
2 77 Power failure
3 0
4 42 Heater broke

1/3 1 0
2 33 Graphics
3 40 Broken wire
4 93 Motor stopped

The table continues covers the entire year and there are actually 9
machines.

I want to extract a report from the data by selecting the machine
number, then select a beginning and ending date and an optional reason
code.

Rows 1through 4 are used for a report as follows:

Row 1 - Report headers as:
A1 - Line number
A2 - Beginning Date
A3 - Ending Date
A4 - Scrap Reason

Row 2 is the search input for the headers in row 1.
Row 3 is where the results will output. Specifically, C4 would be the
total pounds of scrap for the line (machine) specified in B1 and for
the date range given in B2 through B3 inclusive and matching the scrap
reason code specified in B4.

For example, if I put the following in row 2:

B1 - 3
B2 - 1/10/07
B3 - 4/23/07
B4 - Graphics

The formula in C4 would look for all matches of line 3 between the
dates of January 10th through April 23rd, inclusive, and matching the
scrap reason "Graphics."

I would also like the show the total number of occurrences in C5.

Is this possible with a formula in the cell?

Thanks

 

[JP]

First you want to fill down the dates, it's not a "table" unless you
do so.

Here is a formula that can do what you want. Assume your data is from
A6100.

=COUNT((B6:B100="3")*(A6:A100=">1/09/2007")*(A6:A100="<4/24/2007")*(D6100="Graphics")*(C6:C100))

This should be array-entered using Ctrl-Shift-Enter.

All you have to do now is replace the hard-coded data with cell
references. Using your logic below:

=COUNT((B6:B100=B1)*(A6:A100=B2)*(A6:A100=B3)*(D6100=B4)*(C6:C100))

HTH,
JP

 

[JP]

Sorry that should be SUM, not COUNT, in the formulas below.

 

[Summer]

I tried this the way you suggested and with several variations. And,
yes, I did enter the formula as an array; Ctrl+Shift+Enter. Any
suggestions?

 

[JP]

Hi Summer,

Are you saying it didn't work? I tried it on my machine and it worked.
Did you replace the hard-coded data with cell references? When you
array-entered the formula, what happened?


--JP

 

[Summer]

It didn't work. Yes, I replaced the hard coded data with cell
references.

All I get is a zero; the number 0, no matter what line dates or scrap
code I enter.

 

[Summer]

Disregard my last message. It works.

I had the 'less than' and 'greater than' symbols backwards.

Thanks for your help.

 

[JP]

Glad to hear it!

--JP