Hello,

Could someone please help me with the following:

In column L of my worksheet named SA Register is a list of Classes.

In column M of my worksheet named SA Register is a list of Approved
Write-off Values.

In column N of my worksheet named SA Register is a list of Approved Take-up
Values.

I need to detail the following:

1. Items less than $10,000.00, number of cases and total value.

2. Items equal to $10,000.00 but less than $20,000.00, number of cases
and total value.

3. Items equal to $20,000.00 but less than $50,000.00, number of cases
and total value.

4. Items equal to or greater than $50,000.00, number of cases and total
value.

5. Class totals, number of cases and grand total.

Please note that the Approved Take-up Values are only associated with Class
12.

The Approved Write-off Values are associated with all other classes (except
class 12).

Each Approved Write-off and Take-up Value has a class next to it in Column
L.

The output needs to be Print Previewed.

If anyone can help, I will be more than happy to send them a copy of my
spreadsheet on request.

Any help would be greatly appreciated.

Kind regards,

Chris.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Chris Hankin" wrote in message
...
Hello,

Could someone please help me with the following:

In column L of my worksheet named SA Register is a list of Classes.

In column M of my worksheet named SA Register is a list of Approved
Write-off Values.

In column N of my worksheet named SA Register is a list of Approved
Take-up
Values.

I need to detail the following:

1. Items less than $10,000.00, number of cases and total value.

=SUMPRODUCT((L1:L100=12)*(M1:M100<10000))
=SUMPRODUCT((L1:L100=12)*(M1:M100<10000),M1:M100)

2. Items equal to $10,000.00 but less than $20,000.00, number of cases
and total value.

=SUMPRODUCT((L1:L100=12)*(M1:M100<20000))-SUMPRODUCT(--(M1:M100<10000))
=SUMPRODUCT((L1:L100=12)*(M1:M100<20000),M1:M100)-SUMPRODUCT((L1:L100=12)*(M
1:M100<10000),M1:M100)

or

=SUMPRODUCT((L1:L100=12)*(M1:M100=10000)*(M1:M100 <20000))
=SUMPRODUCT((L1:L100=12)*(M1:M100=10000)*(M1:M100 <20000),M1:M100)


3. Items equal to $20,000.00 but less than $50,000.00, number of cases
and total value.

You should be able to work it out by now.

4. Items equal to or greater than $50,000.00, number of cases and
total
value.

Ditto.

5. Class totals, number of cases and grand total.


=SUMPRODUCT(--(L1:L100=12))
=SUMPRODUCT(--(L1:L100=12),M1:M100)

Please note that the Approved Take-up Values are only associated with
Class
12.

The Approved Write-off Values are associated with all other classes
(except
class 12).

Each Approved Write-off and Take-up Value has a class next to it in Column
L.

The output needs to be Print Previewed.

If anyone can help, I will be more than happy to send them a copy of my
spreadsheet on request.

Any help would be greatly appreciated.

Kind regards,

Chris.

Thanks Bob for your help, much appreciated.

I looked up the function SUMPRODUCT in the Excel help section. It
mentions arrays. Do I need to enter the formulas in as array formulas?

Can I run this as a macro?

I'd like to run it as a macro if possible and output to a print-preview.

Kind regards,

Chris.

P.S. I very much a newbie - please be gentle.


*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

No it doesn't have to be entered as an array formula as it is a formula that
works on arrays, like SUM.

Why bother with a macro? You could set it up and print preview as normal.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Chris Hankin" wrote in message
...

Thanks Bob for your help, much appreciated.

I looked up the function SUMPRODUCT in the Excel help section. It
mentions arrays. Do I need to enter the formulas in as array formulas?

Can I run this as a macro?

I'd like to run it as a macro if possible and output to a print-preview.

Kind regards,

Chris.

P.S. I very much a newbie - please be gentle.


*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!