If Then Else Help Please

C

Chris Hankin

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

Bob Phillips

--

HTH

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

Chris Hankin said:
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.
Click to expand...

You should be able to work it out by now.
 
C

Chris Hankin

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!
 
B

Bob Phillips

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)
 
Top