I need to calculate the totals from a excel download of sage line 50.
The totals required are SumIF Department=x and Nominal=y
Can anybody tell me the formula for Multiple criteria calculations such as the above.
Many thanks in advance
Mark Rolinson
Replies (12)
Please login or register to join the discussion.
Never use SUMIF seems a bit harsh
Your suggested formula looks to be the array formula suggestions without having to remember to Ctrl Shift Enter to make it work.
Alan is correct
Sumproduct() compiles and runs in excel exactly as if it has been {Sum()} and is a convenient alternative if you don't want to control+shift. It is however a bit more restrictive in its application in more general cases and the array entered is more flexible in other cases. Sumproduct would work fine in this example case
Different Function to do what you want
Hi Guys,
Well you should never ever use SumIf
The best function to use here is actually the Sumproduct Function.
To do what you want try this example
=Sumproduct((a1=OtherSheet!a1:a1000)*(b1=OtherSheet!b1:b1000)*(c1=OtherSheet!c1:c1000))
This can be used for laosd of criteria up to 30 I think though you should limit it to 5 max or the calculation will take for ever.
rgds
Ollie
What's the best way to to produce a management pack?
Mark, you've raised the point that pivot tables are great for a quick and dirty report, but you can't format them to look good enough for regular management reporting.
So what packages are people actually using to produce monthly management packs - nice looking reports, managers allowed to view only their own data, available over the company intranet, etc etc?
Does anyone use Business Intelligence packages such as those advertised on ExcelZone - XLCubed, o2Olap, FRX, XRL Reporter?
Or can you do the job just with Excel?
As Clint said at the start, other than a Pivottable, that'll be
Possibly in the Productivity link but anyway here goes.
For ease create 3 named ranges being the columns with:
Department data in called sDept
Nominal data in called sNom; and
Amount data in called sAmount.
These should all be one column wide, and start and end on the same rows. It is easier to fix these as named ranges than drag & select ranges, which may not be absoulte if you copy & paste elsewhere and so give erroneous results.
Now where you want the totals you can use the formula
=SUM((sDept=x)*(sNom=y)*sAmount)
BUT instead of just pressing Enter to accept the formula - which will give the result #VALUE - Hold Ctrl + Shift and press enter.
When you look at the formula bar after doing this it will show
{=SUM((sDept=x)*(sNom=y)*sAmount)}
which indicates an Array formula.
What Excel is doing in this instance is row by row
checking the entry for the row it is on in the range sDept to see if it equals x. If it does the result is 1 else 0.
It then checks the entry on the same row in sNom in the same way and multiplies the result.
So if both are TRUE you get 1*1 = 1, otherwise you get 0*1 = 0 or 1*0 = 0
Finally it multiplies the entry for that row in the range sAmount by the result of the previous two items
Thus if both the entry in sDept and sNom are TRUE you get 1*the entry in sAmount = sAmount. Otherwise you get 0
Excel then moves to the next row and does the same thing for each row, summing the result for each row (either 0 or sAmount) as it goes.
And there you are - clear as mud probably, but try it, it may become clearer
The benefit of this over a pivottable is that you can use the formula anywhere and are not restricted to the table
Something like
=SUM((Department = x)*(Nominal = y))
entered as an array formula
To enter a formula as an array formula hold down the Control and Shift keys, while pressing enter.
When you see the formula presented on the formula bar it will look like
{=SUM((Department = x)*(Nominal = y))}
Note the curly brackets. You do not enter the, they are only for display purposes.
Named ranges Department and Nominal should be the same dimensions.
Conditional sum wizard
You may find the conditional sum wizard helps in the creation of this sort of formula. Look in the Tools menu for 'Conditional sum...'. If it's not there, go to Tools, Add-Ins. The Conditional Sum Wizard should be one of the options.
Hope it helps
Simon Hurst
Shameless self-promotion
Should have mentioned that there are more details regarding the use of the Conditional Sum Wizard and using Excel to work with accounting data in the spreadsheet bit of the Office Productivity Kit...
https://www.accountingweb.co.uk/resources/office_prod.html
Simon
use a pivot table
Why all these complicated formulas? A pivot table will give you the answer without needing any formulas at all.
Pivot tables are fine
although I question whether they are less complicated than the array formula. Not that I am saying they are complicated ... in absolute terms neither is.
Minor niggle with pivot tables is that they need refreshing, where the array formula will update on each workbook recalculation.
concatenation
you could also try text concatention by putting the keys together into one cell, which would enable the sumif to work as normal.