View Single Post
  #13   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Sandy Mann Sandy Mann is offline
external usenet poster
 
Posts: 2,345
Default Count and Sum functions with 2 criterias

The only difficulty is that fact that you do not appear to be using the
Order Numbers in the formula requirement and that is the only column common
to both sheets.

With the Columns Headers you give in Column A, B & C in both sheets and with
a Customer's name in F2 of Database3 use the following formulas in Database
3:

1. Billed -
Number of Orders: =COUNTIF('Database 2'!B2:B300,F2)

Total Billed Amount:
=SUMPRODUCT(('Database 2'!B2:B300=F2)*('Database 2'!C2:C300))

2. Paid -
Number of Orders:
=SUMPRODUCT((('Database 2'!B2:B300=F2)*('Database 2'!A2:A300)=
('Database 3'!A2:A300))*('Database 3'!B2:B300="Paid"))

Total Billed Amount:
=SUMPRODUCT((('Database 2'!B2:B300=F2)*('Database 2'!A2:A300)=
('Database 3'!A2:A300))*('Database 3'!B2:B300="Paid")*'Database 3'!C2:C300)

3. Rejected -
Number of Orders:
=SUMPRODUCT((('Database 2'!B2:B300=F2)*('Database 2'!A2:A300)=
('Database 3'!A2:A300))*('Database 3'!B2:B300="Rejected"))

Total Billed Amount:
=SUMPRODUCT((('Database 2'!B2:B300=F2)*('Database 2'!A2:A300)=
('Database 3'!A2:A300))*('Database 3'!B2:B300="Rejected")*
'Database 3'!C2:C300)

4. % Paid: =<Billed Number of Oders above / <Paid Total Billed Amount
above
and format as Percentage.

It is not necessary to reference the sheet that the formula is in but I
have included them above for clarity. Without using the host sheet the
formulas a

2. Paid -
Number of Orders:
=SUMPRODUCT((('Database 2'!B2:B300=F2)*('Database 2'!A2:A300)=
(A2:A300))*(B2:B300="Paid"))

Total Billed Amount:
=SUMPRODUCT((('Database 2'!B2:B300=F2)*('Database 2'!A2:A300)=
(A2:A300))*(B2:B300="Paid")*C2:C300)

3. Rejected -
Number of Orders:
=SUMPRODUCT((('Database 2'!B2:B300=F2)*('Database 2'!A2:A300)=
(A2:A300))*(B2:B300="Rejected"))

Total Billed Amount:
=SUMPRODUCT((('Database 2'!B2:B300=F2)*('Database 2'!A2:A300)=
(A2:A300))*(B2:B300="Rejected")*C2:C300)


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