SUMIF Formula for 2 criteria

SUMIF Formula for 2 criteria

Didn't find your answer?

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.

avatar
By ACDWebb
10th Feb 2006 19:35

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.

Thanks (0)
avatar
By AnonymousUser
11th Feb 2006 10:38

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

Thanks (0)
avatar
By oliver.jennings
10th Feb 2006 15:41

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

Thanks (0)
avatar
By David Carter
02nd Feb 2006 21:35

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?

Thanks (0)
avatar
By MRolinson
02nd Feb 2006 11:50

Thankyou
Great feedback so many thanks.

For those wondering, I had already created a pivot table to give me the answer in an instant, however I needed to create and automated monthly management report and as far as i'm aware you can't format pivot tables hardly at all (I mean insert Rows, Labels ETC - I tried this for about 2 hours prior to posting - hehe).

Anyway, I managed to achieve my objective primarily using the logic from Alan and also the conditional sum wizard which is great. Took alot of trial and error as I ran into the common "Cell format" problem that occurs with sage extracts.

Many thanks again

Regards
Mark

Thanks (0)
avatar
By ACDWebb
01st Feb 2006 23:52

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

Thanks (0)
avatar
By AnonymousUser
01st Feb 2006 17:14

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.

Thanks (0)
Simon Hurst
By Simon Hurst
01st Feb 2006 19:59

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

Thanks (0)
Simon Hurst
By Simon Hurst
01st Feb 2006 20:18

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

Thanks (0)
avatar
By David Carter
01st Feb 2006 22:27

use a pivot table
Why all these complicated formulas? A pivot table will give you the answer without needing any formulas at all.

Thanks (0)
avatar
By AnonymousUser
02nd Feb 2006 09:20

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.

Thanks (0)
avatar
By listerramjet
02nd Feb 2006 08:52

concatenation
you could also try text concatention by putting the keys together into one cell, which would enable the sumif to work as normal.

Thanks (0)