Sumproduct not working

joand

Active Member
Joined
Sep 18, 2003
Messages
267
Hello guys,
I want to do a lookup with multiple criteria
In C34:AB34 are the Months, In B30 is the lookup value for Month
In B36:B56 are the Company Names, In C30 to X30 lookup values for Company Name
In C35:AB35 are the Columns for Billing and Collection, in B31 is the lookup value for Billing, and B32 is the lookup value for collection.

Billing criteria returns the right values. For collection, however it is not and I wonder what is wrong with it?

The formula I used is =SUMPRODUCT(($B$36:$B$56=C$30)*($C$35:$AB$35=$B32)*($C$34:$AB$34=$B$30),$C$36:$AB$56)

Anyone got better ideas?

di-7MWT.jpg
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Hello guys,
I want to do a lookup with multiple criteria
In C34:AB34 are the Months, In B30 is the lookup value for Month
In B36:B56 are the Company Names, In C30 to X30 lookup values for Company Name
In C35:AB35 are the Columns for Billing and Collection, in B31 is the lookup value for Billing, and B32 is the lookup value for collection.

Billing criteria returns the right values. For collection, however it is not and I wonder what is wrong with it?

The formula I used is =SUMPRODUCT(($B$36:$B$56=C$30)*($C$35:$AB$35=$B32)*($C$34:$AB$34=$B$30),$C$36:$AB$56)

Anyone got better ideas?

Are you wanting to sum $C$36:$AB$56 under the specified conditions?
 
Upvote 0
Hi Aladin, i just want to return the value specified by the 3 criteria: (1) Month, (2) Company Name and (3) Billing or collection. Any method will do.
 
Upvote 0
Hi Aladin, i just want to return the value specified by the 3 criteria: (1) Month, (2) Company Name and (3) Billing or collection. Any method will do.
 
Upvote 0
Hi Aladin, i just want to return the value specified by the 3 criteria: (1) Month, (2) Company Name and (3) Billing or collection. Any method will do.

I guess you have a smilar formula in C31 for Billing and that works as intended. But it does not in C32, right?

If you unmerge C34:D34 and repeat the date of C34 in D34 plus do so for all merged cells, the formula you have will succeed.
 
Upvote 0
Or you could have just offset the array in the 3rd argument 1 column left

=SUMPRODUCT(($B$36:$B$56=C$30)*($C$35:$AB$35=$B32)*($B$34:$AA$34=$B$30),$C$36:$AB$56)
 
Upvote 0

Forum statistics

Threads
1,224,520
Messages
6,179,267
Members
452,902
Latest member
Knuddeluff

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top