COUNTIF and SUMIF where the target cells cover more than 1 column

Hi,

I have been tasked with automating a report in work and I am struggling to link up parts of the data using formulas.

I can count easily enough the instances where there are straightforward counts using SUMIF and COUNTIF to basically match the data on tabs 1 and 2 with the Summary, however, in instances where one of the people has a shared job with another person I need to count this separate in the Summary tab and to show that the shared job is split e.g. Person A and Person share a job, each needs to receive 50% of the value and to have the count of jobs they've done reflect that it is a 0.5 and not 1.

I don't know if I am missing something extremely obvious and I should just be using SUMIFS and COUNTIFS, but they don't seem to do what I need. I have tried using AND and OR alterations but they just bring back True or False and not the values I need.

Please can you help me?!

Thank you.

Apologies,

3rd Tab 'Summary' - specifically column range D-K.

D-G looks at Tab 1 'IT' - need to count jobs completed as a whole and where applicable on shared jobs for half the value and half the job count.

H-K looks at Tab 2 'Perm Corp Accounts' - same as above, needs to count whole jobs and jobs which are split.

Thank you.

Hi,

Thank you for coming back to me.

I don't think it does do what I need it to do.

By way of example I need it to do the following:

Row 6 on the IT Tab should show that as it was Person A as both the first person and the second, therefore on the summary this should show in row D and E - Number of Deals = 1, Sale Value = £1,352. Which it does, but then, on row 8 of the IT Tab Person A is the second person on the deal, so in columns F & G it should pull through as - Number of Deals = 0.5, Sale Value = £744.50

Therefore, Person A should have - Total Sales 1.5, Salve Value £2096.50

The formulas you've suggested work, but in effect double count the split deals I have.

e.g.

Corporate Recruiters 0 £0.00 No.of Sales Sale value No.of Sales Sale value
Person A 2.5 £3,448.50 1 £1,352.00 1.5 £2,096.50

Person A
Should be 1.5 £2096.50 1 £1,352.00 0.5 £744.50

Person B 7 £30,142.32 3 £11,569.12 3 £17,115.20
Person C 2.5 £6,812.98 1 £1,489.01 1.5 £5,323.97
Person D 6 £25,298.44 3 £10,132.37 2 £11,602.07

I need the formula to ID the person in each section, and then adjust values accordingly.

Thank you for your help!