Excel 365 - Multiple SUMIFs with percentages

[pendle666]

Hello

I'm trying to total some items for a report. I have a table of a number of items with values, I'm interested in these ones:
[pre]B C
SPP 344.96
SMP 0.00
NICs 50,000
[/pre]

In my totals area I want to add SMP and SPP and calculate 92% of that total and then add the total of NICs.

In my total cell C94 I have the following formulae:

=SUMIF(B5:B85,"SPP",C5:C85)*OR(B5:B85,"SMP",C5:C85)*92%

Which gives me the £317.36 value that I need, but I can't get the NICs value in. I've tried adding another *OR and *AND as well as putting NICS before or after, but it's not made any difference to the result.

Can someone advise?

thanks

thank you for helping

____________
Pendle

 

[Andrzejek]

Are you looking for a final value of 50317.3632?

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[pendle666]

Yes, rounded for pence - £50,317.36



thank you for helping

____________
Pendle

 

[Andrzejek]

I don't know what are your headings for columns A, B, and C, but what I did was:

I used NameRange for column B as MyColB and for col C is MyColC (you should name them differently based on your col headings...) so my formule is:
[blue]
[tt]=((SUMIF(MyColB, "SPP", MyColC) + SUMIF(MyColB, "SMP", MyColC)) * 0.92) + SUMIF(MyColB, "NICs", MyColC)[/tt][/blue]

Instead of hard-coded 0.92, I would use another named range (TaxRate?), one cell, and use that in my formula.

With Named Ranges - when you click on a cell with this formula - you get a nice, colored picture of what's going on:

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[pendle666]

Excellent - thank you so much.

The tax rate is a reclaim value and it's been that for over 15 years - I doubt it'll change!

thank you for helping

____________
Pendle

 

[combo]

You can also calculate the result in pivot table using calculated fields:
1. create pivot table, with 'B' as rows, 'C' as values, aggregated with sum,
2. add calculated item (select any row entry), add name 'Result' with formula: = (SMP +SPP )*0.92+NICs
3. filter row field and leave only 'Result'
After some formatting of pivot table (no totals, cleaned 'display' tab):

There is no rounding.

combo