Combining Sumproduct Result for Three or More Cells

Hi All,

Please I need help with SUMPRODUCT formula.

I am trying to calculate Income tax for each category of employees and then add the results up to get a TOTAL income tax for all employees.

Here is the calculation for each category of employees, using sumproduct. Each of them gives the correct tax payable for each category:

a) Income Tax for Owner – Annual Salary is in Cell R8:
=SUMPRODUCT(--(R8>{10600;31785;150000}), (R8-{10600;31785;150000}), {0.2;0.2;0.05})

b) Income tax for Permenent Staff – Annual Salary is in Cell R9:
=SUMPRODUCT(--(R9>{10600;31785;150000}), (R9-{10600;31785;150000}), {0.2;0.2;0.05})

c) Income tax for Part-time Staff – Annual Salary is in Cell R10:
=SUMPRODUCT(--(R10>{10600;31785;150000}), (R10-{10600;31785;150000}), {0.2;0.2;0.05})

1.My problem is how to combine all the three formulas into one formula by including the cell references into one formula.

2.As you can see, apart from the cell numbers (R8, R9, R10), every other thing in the formula is the same for each of the groups.

3.If I have to get a combined PAYE total for all the staff, I am tempted to combine all the three formulas with a ‘plus’ as follows:

=SUMPRODUCT(--(R8>{10600;31785;150000}), (R8-{10600;31785;150000}), {0.2;0.2;0.05})+ SUMPRODUCT(--(R9>{10600;31785;150000}), (R9-{10600;31785;150000}), {0.2;0.2;0.05})+ SUMPRODUCT(--(R10>{10600;31785;150000}), (R10-{10600;31785;150000}), {0.2;0.2;0.05})

4.Please, how can I summarise the formula by and make it shorter by grouping the cell numbers together with just one set of formula without making it as long as it is above?

5.I have tried something (see below) but I can’t get it to work correctly:

=SUMPRODUCT(--(R8;R9;R10)>{10600;31785;150000}), (R8;R9;R10)-{10600;31785;150000}), {0.2;0.2;0.05})

Please kindly help.

Thank you.

Buddy8

If you already have the 3 separate formulas, why not just add the results?
=SUM(A1:A3)

Where each of the previous formulas are in A1 A2 and A3

Hi TMS,

Thanks for your kind response.

I have tried the two versions of the SUMPRODUCT formula that you kindly provided. Unfortunately, none of them provided the desired result.

I have therefore attached a sample workbook that clearly demonstrates what I need.

As can be seen from the attached workbook, if I apply the SUMPRODUCT formula separately for each of the salary categories, each of them produces the correct PAYE tax calculation as shown in Cells B14 to B16, giving a total tax of £13,103 as shown in B17.

However, instead of calculating them separately, I would like to use just one formula in any one cell, in which B5, B6 and B7 are referenced, with the rest of the formula remaining the same, so as to produce the same result of £13,103.

I look forward to some help with this. Thanks for everything.

Buddy8

Hi Junmo1,

Thanks for your kind response. Please see my response (Post #4) to TMS. I do not wish to do the calculations separately and then add the results up as you suggested. I am looking for a way to combine the formula in just one cell to get the desired result.

Thanks.

Buddy8

Originally Posted by Buddy8

I am looking for a way to combine the formula in just one cell to get the desired result.

But Why?

1. Splitting calcuations into multiple cells (using helper cells) is NOT a bad thing.
Excel gives you 1048576 rows and 16384 columns, that's 17,179,869,184 cells, in just one sheet.
Might as well use a few of them.

2. It seems very unlikely to me that any reporting type of sheet that has information broken into categories to begin with, would need to summarize the TOTAL, but not each individual category as well.