Sumproduct Issue

  • Thread starter Thread starter Dominique Feteau
  • Start date Start date
D

Dominique Feteau

I have a table that has 3 data validation lists: Billing Type, Billing
Category & Month Billed.

How do I set up a sumproduct formula to all up all the amounts associated
with those 3 lists. For example: I want it to count up all the Direct
(Billing Type), New One-Time (Billing Category) in January (Month Billed).
 
Hi
if you have your data in columns A:D and your drop down in E1:G1 try
=SUMPRODUCT(--(A1:A100=E1),--(B1:B100=F1),--(C1:C100=G1),D1:D100)
 
=SUMPRODUCT(--(A1:A10=H1),(B1:B10))+SUMPRODUCT(--(C1:C10=H2),(D1:D10))+SUMPR
ODUCT(--(E1:E10=H3),(F1:F10))

where A,C,E hold the test values, B,D,F are the amounts and H1, H2, H3 are
the DV cells.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Client Name Billing Type Billing Category Date Work Completed Month
Billed Amount
ABC Company Direct New One-Time January 1000
XYZ Company In-Direct Renewal One-Time January 200
GHI Company Direct Renewal Recurring February 5000
TUV Company In-Direct New One-Time February 60000


This is what my original table looks like.

And this is the formula I'm using: =SUMPRODUCT(('AonLine
Clients'!A4:F40)="In-Direct",('AonLine Clients'!A4:F40)="Renewal
One-Time",('AonLine Clients'!A4:F40)="Febuary")

I'm getting a value of 0. What am I doing wrong?
 
Hi
1. NEVER attach files to this NG!

2. Why don't you use the formula as provided to you. The '--' and the
order of parenthesis was there for a good reason. also your ranges are
not correct.
Your formula now looks like:
=SUMPRODUCT(((Clients!B4:H40)="In-Direct"),((Clients!B4:H40)="Renewal
One-Time"),((Clients!B4:H40)="Febuary"),Clients!B4:H40)

Try changing this to
=SUMPRODUCT(--(Clients!D4:D40="In-Direct"),--(Clients!E4:E40="Renewal
One-Time"),--(Clients!G4:G40="February"),Clients!H4:H40)
of course in your sample sheet you have to change at least on month to
'February'

Though I think the following formula is even beter for your layout.
Enter the following exactly as provided in cell B7 of your summary
sheet:
=SUMPRODUCT(--(Clients!$D$4:$D$40=TRIM(LEFT($A7,FIND(" -
",$A7)-1))),--(Clients!$E$4:$E$40=TRIM(MID($A7,FIND(" -
",$A7)+3,100))),--(Clients!$G$4:$G$40=B$5),Clients!$H$4:$H$40)

You may have to change the drop-down of column E to EXACTLY match the
values in column A right to the dash 8currently you have for example
'Renewals One-Time' in column A of your summary sheet but
'Renewal One-Time' in your drop down
Make this equal!

But I really would use a pivot table for this
 
Sorry Frank. I didnt know. After you said that, I can understand why.

Thanks for you help and I'll try to persuade them to use pivot tables. I
know how to use those pretty well.

Niq
 
Back
Top