Sumproduct to sum multiple column of data based on multiple criteria on different columns

AndewY

New Member
Joined
Sep 24, 2020
Messages
2
Office Version
  1. 2010
Platform
  1. Windows
Hi,
I'm trying to sum numbers on a set of columns based on corresponding cell contents on different columns.
One type of criteria is that the cell does not contain certain text.

I have work will this.

=SUMPRODUCT((C2:C3000=O11)*(Z2:BU3000)
*(ISNUMBER(SEARCH({"*OT*","*TOIL*"},L2:L3000))))

Where C2:C3000 is column containing names
Z2:BU3000 are the multiple columns of numbers that need to be summed
and L2:L3000 is column containing comments.

I hope that my explanation above doesn't confuse and thanks in advance!
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

AndewY

New Member
Joined
Sep 24, 2020
Messages
2
Office Version
  1. 2010
Platform
  1. Windows
Sorry for the multiple posts.
I'm getting #N/A as a result.
Have tried using {SUM(IF()} but then couldn't get the formula to excluded where cells contained certain text e.g. <>"*OT*" returned an error.

Thanks again
 

Watch MrExcel Video

Forum statistics

Threads
1,128,173
Messages
5,629,162
Members
416,368
Latest member
PaoloC

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
Top