sumproduct() and search()

Hi,

I'm having a problem with the formula listed below:

=SUMIF((Weekly_Upload_Export_!AM:AM="DC By Pass")*(Weekly_Upload_Export_!AL:AL="*(Corporate)*")*Weekly_Upload_Export_!AE:AE)

What I'm trying to do is sum Column AE if column AM = DC By Pass and Column AL has the text (Corporate) in the cell. I can't get it to work.

Any thoughts.

Thanks

Eddie

Thank you so much for your quick reply.

I can't get it to work. It is returning #NUM!

Any ideas of what it is not working? This really seems easy to me but I can't get it to work.

Thanks

Eddie

Well with sumproduct(), there are quite often several parameters to check ...

Do you mind uploading a zipped copy of your spreadsheet, for a precise answer ...

HTH
Carim

Yeah I will. I have to run to a meeting. I'll put it up when I get back.

Thank you for your help.

Eddie.

You can't use whole column references in Sumproduct...

Richard,

You are right ... copied formula in a hurry without even noticing it ...!!!

Cheers
Carim

Thanks. We are getting closer. Here is the formula that I have on the sheet now:

=SUMPRODUCT(--(Weekly_Upload_Export_!AM2:AM65536="DC By Pass")*--(Weekly_Upload_Export_!AL2:AL65536="*(Corporate)*")--(Weekly_Upload_Export_!AE2:AE65536))

It seems to be adding the totals of AM and AL if they have. I only want them to sum if AM has DC by pass and AL has corporate.

Thank you

Eddie.

I meant to put this in the post above. AL should sum if the text corporate is in the cell. That is why I have wildcard things around it.

Thanks

Hi Eddie,

Is the word "Corporate" anywhere in the cell ... or always located in the same position ... ?
You could use the Search() function ...

HTH
Carim

Here is a copy of the file. There is usually a lot more data but I had to throw most of it out to get it to attach.

The formula should go in B9 of the summary tab.

Thank you for your help.

Eddie.

Hi Eddie,

Glad you posted a sample worksheet, it makes life a lot easier ...

Attached is your tailor-made sumproduct() formula ...

HTH
Carim

Glad your problem is fixed.

Carim

I have faith you got it to work. I just haven't had a chance to check it out test it yet. Thank you so much for your help. You should have some feedback in a little while.

Eddie.

That worked great. Thank you so very much.

Eddie.

Glad your problem is now totally fixed.

Thanks a lot for the feedback

Carim