ExcelBanter - View Single Post

Sue

I sent the entire workbook to

"Sandy Mann" wrote:

"Sue" wrote in message
...
But when I tried the other formulae, the result for No. of orders is
giving
me a #NA and the billed amounts is giving a '0'. Not sure if im doing
something wrong.

I'm not sure either. If I have any #N/A error returned in Column G I get
#N/A errors all but the "Billed" formulas.

Can you send me an example of the sheet, sanatised of sensitive data if
necessary. Don't click on the *Reply* button because that will sent your
reply to *maininator.com* which is a spam trap. Change the Mailinator.com
to Tiscali.co.uk as it says in my signature.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

Replace @mailinator.com with @tiscali.co.uk

"Sue" wrote in message
...
Hi,

The Lookup formula did work and gave me the list of the customers matching
that order number in column G. Each customer (say 10 customers) can have
various order numbers(say 500 different order numbers).

But when I tried the other formulae, the result for No. of orders is
giving
me a #NA and the billed amounts is giving a '0'. Not sure if im doing
something wrong.

--
Sue

"Sandy Mann" wrote:

Sue,

Having slept on it I think that my suggestion was not a bad idea - it was
a
TERRIBLE idea. Not only does it have the failing listed previously but
it
also returns wrong results if the order of Order Numbers is not the same
in
both sheets.

I would *strongly* recommend that you use a "Helper" column in Database
3.
I used Column G but any column will do and if you want you can hide the
"Helper" column.

In G2 of Database 3 enter the formula:

=IF(A2="","",VLOOKUP(A2,'Database 2'!$A$2:$B$30,2,FALSE))
and copy down as far as required.

Then enter a list of Customer Names in Column H starting from H2

The "Billed" formulas are OK because they only reference Database 2 but
change the other formulas as follows:

In the same Row as the Billed Formulas enter:

Paid -
Number of Orders:
=SUMPRODUCT(('Database 3'!$G$2:$G$300=H2)*('Database
3'!$B$2:$B$300="Paid"))

Total Billed Amount:
=SUMPRODUCT(('Database 3'!$G$2:$G$300=H2)*('Database
3'!$B$2:$B$300="Paid")*'Database 3'!$C$2:$C$300)

Rejected -
Number of Orders:
=SUMPRODUCT(('Database 3'!$G$2:$G$300=H2)*('Database
3'!$B$2:$B$300="Rejected"))

Total Billed Amount:
=SUMPRODUCT(('Database 3'!$G$2:$G$300=H2)*('Database
3'!$B$2:$B$300="Rejected")*'Database 3'!$C$2:$C$300)

The ranges are Absolute so that you can drag down on the fill handle for
the
other Customer Names in Column H.

I have deliberately left the Formulas in Column G so that they will
return a
#N/A error if no match is found. This is because if it returned an empty
string then the above formulas would ignore that entry and thus again
return
a wrong result. As it is the above formulas will echo the #N/A errors
returned by Column G.

My apologies if I have caused any confusion.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

Replace @mailinator.com with @tiscali.co.uk

"Sandy Mann" wrote in message
...
One caveat to my previous suggestion, if you have more than one entry
of
the *same* Order Number for any Customer then the formulas will return
wrong results. It would be far better therefore to have the names of
Customes in the Rows of Database 3 as well.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

Replace @mailinator.com with @tiscali.co.uk

"Sue" wrote in message
...
Hi Sandy,
Thanks for your help! I have another one for you, can you please help
me
with this one too?
I have two worksheets, the first one (say Database 2) has 4 columns -
ORDER
NUMBER, CUSTOMER, BILL AMOUNT, BILL DATE
The second one (say Database 3) has 4 columns - ORDER NUMBER, PAID/
REJECTED, PAID AMOUNT, PAID/REJECTED DATE

I want to make a new one with the following information:
For each Customer
1. Billed - Number of Orders, Total Billed Amount
2. Paid - Number of Orders, Total Billed Amount
3. Rejected - Number of Orders, Total Billed Amount
4. % Paid

--
Sue

"Sandy Mann" wrote:

I'm glad about that because you had me scratching my head. Good to
hear
that you got it sorted out.

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

Replace @mailinator.com with @tiscali.co.uk

"Sue" wrote in message
...
Hi Sandy, pls ignore my previous message, it works now. Must have
had
some
cell ref wrong..
--
Sue

"Sandy Mann" wrote:

Very true Pete, I also forgot to point out that I used Absolute
ranges so
that the formula could be dragged down.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

Replace @mailinator.com with @tiscali.co.uk

"Pete_UK" wrote in message
...
If you put your category range in two columns like this:

1 25
26 50
51 100
101 250

you could then simplify the formulae that Sandy gave you by
refering
to the cells containing the range rather than include them
explicitly
in the formulae.

Hope this helps.

Pete

On Oct 7, 10:44 am, Sue wrote:
Let me give an example, Say the Data looks like this:

Category Processed
2 Yes
50 No
40 Yes
25 Yes
90 No
7102 Yes
198 Yes
648 No

The Format that I would like is as follows:

Category Processed (Yes) Not
Processed(No)
Count Sum(Amt) Count
Sum(Amt)
1 - 25 2 27 0
0
26 - 50
51 - 100
101 - 250
Total

I hope that helps..
--
Sue

"Bob Phillips" wrote:
Your question is not clear, at least to me.

I am not seeing where the variable data is and hat is
repeated,
so
what
needs to be counted/summed?

--
__________________________________
HTH

Bob

"Sue" wrote in message
...
Question:
Category Processed (Yes) Not Processed(No)
Count Sum(Amt) Count Sum(Amt)
1 - 25
26 - 50
51 - 100
101 - 250
Total

Data has two columns which are Amount and Status(Yes/No).
Can
someone
help
me with the formulae to count the number of coumns which
fall
in
each
Category and also sum of the amounts in those categories?
--
Sue- Hide quoted text -

- Show quoted text -