Sumproduct formula needs to halve a value depending on criteria

**3smees23** · 08-10-2009, 01:12 PM

Hi there,

You guys very kindly helped me with a spreadsheet last week.

The current spreadsheets add up each persons totals by matching the name in each tab with the name of the person who won the job located in service orders tab.

BUT.....If two salesman pair up on up on a job then the formula doesn't recognize the joint name. eg Scott/Ash in row 21 & 22 (Service orders).

I need the totals to half the job and add it to the salesmans total accordingly.

You helped me before and i have tried to adapt a formula you wrote.....

Please Login or Register  to view this content.

Please can someone get the above code working. I have attached the spreadsheet.

Thanks

Sam

**DonkeyOte** · 08-10-2009, 01:26 PM

I confess as far as I recall we resolved this exact question last week...

Please Login or Register  to view this content.

above includes you Contract clause not present in the original formula

As mentioned last week, IMO, you'd be far better served storing count of sales persons on any given line on 'Service Orders' sheet in a separate column, eg:

Please Login or Register  to view this content.

this would enable you to improve the performance slightly of your SUMPRODUCT, with the earlier formula becoming:

Please Login or Register  to view this content.

**3smees23** · 08-11-2009, 09:28 AM

OK Thanks,

I have gone for the first option as i think this is perfect for what im trying to achieve. The only problem is that all the dates need to be the 1st of each month in order for the formula to add it up.

For example row 42 on service orders is set to a date of the 2nd of Aug and its therefore not getting calculated in Scotts totals.

Thanks in advance

Sam

**DonkeyOte** · 08-11-2009, 09:58 AM

In your original thread I think I provided you with the following approach:

Please Login or Register  to view this content.

there are various methods but if always looking for 1st of Month and the date range only ever contains blanks or numners the above approach will work well (and negates need for two tests (month & year) and/or text coercion test)