Sumproduct Issue

[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).

 

[Frank Kabel]

Hi
if you have your data in columns A and your drop down in E1:G1 try
=SUMPRODUCT(--(A1:A100=E1),--(B1:B100=F1),--(C1:C100=G1),D1100)

 

[Bob Phillips]

=SUMPRODUCT(--(A1:A10=H1),(B1:B10))+SUMPRODUCT(--(C1:C10=H2),(D110))+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)

 

[Dominique Feteau]

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?

 

[Frank Kabel]

Hi
try
=SUMPRODUCT(--('AonLine Clients'!B4:B40="In-Direct"),--('AonLine
Clients'!C4:C40="Renewal
One-Time"),--('AonLine Clients'!E4:E40="Febuary"),'AonLine
Clients'!F4:F40)

if I've identified your correct columns
You may also consider using pivot tables for this. See:
http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html
http://www.ozgrid.com/Excel/excel-pivot-tables.htm

 

[Frank Kabel]

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!D440="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

 

[Dominique Feteau]

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