Sumproduct with Left Function

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? However, please give us an indication of the approximate number of rows of data you want the solution to work with (100, 1000, 100,000 or whatever). Please don't attach a picture of an Excel sheet (no-one will want to re-type all your stuff before starting).

1. It does NOT have to be your real sheet - mock up a SAMPLE if you need to. But not 1000's of rows!!! It makes manual checking so tedious. Whatever you do... make sure that all confidential information is removed first!!

2. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

3. Make sure that your desired solution is also shown (mock up the results manually).

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Welcome to the forum.

=SUMPRODUCT(--(MONTH(N7:N17)=MONTH(TODAY())*(--(LEFT(M7:M17,1)="8"))))

Try, untested:

Formula:

Please Login or Register  to view this content.

Isn't this simply

=SUMPRODUCT(--(MONTH(N7:N17)=MONTH(TODAY()))*(LEFT(M7:M17,1)="8"))

Isn't that what I said in post #3?

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Originally Posted by AliGW

Isn't that what I said in post #3?

Who me?
Yeah got delayed with a phone call so didnt see your reply till I finished posting mine after the call.

In theory, you don't need the double negative AND the multiplication operations.

The double negative, as an arithmetic function, converts the string of TRUE/FALSE to ones and zeroes. SUMPRODUCT then multiplies the arrays. The asterisk multiplication should do that in one go but I’m never convinced. My preference is the double negative.

Trevor - that's a good point.

Special-K - who, you?

Originally Posted by AliGW

Special-K - who, you?

I've no idea half the time (I'll cut down on the beer)!

As simple as that. Thanks very much - that's perfect!

Not sure which answer that applied to but, no worries, you have a solution.


If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.