SUM IF EXCEL FUNCTION

[dilworth]

I am analysing data in a spreadsheet and need to group together records-the problem is the data is exported from an application and therefore the data is dynamic-also the application creates section headings that make it difficult to sort the data.

I need to group areas into regions then sum values by region-the source data is formatted as follows:

BASIC COST

Area 1 250


Area 7 50
Area 2 750
Area 5 95

FULL COST

note this is exclusive of VAT

Area 9 50
Area 15 150
Area 10 575
Area 2 250

This is just a small part of the data-I want to group the areas into regions then sum the amounts by region

e.g. Region A should contain Area 10 and Area 2-the sum of Region A would therefore be 1575-I have tried sumif but this only seems to work for single criteria-and this needs to be for more than one area.

I have tried pivot tables but this is difficult due to the data changing and the various headings in the data.

Any suggestions would be appreciated.

 

[RivetHed]

Have you had a look at Data>>Subtotals?

 

[dilworth]

Thanks-I have tried this-does the data need to be grouped together in order for this to work correctly as the areas that need to be grouped together are not in any order and the rows are seperated by various sub headings e.g.

COLUMN A COLUMN B

BASIC COST

Area 1 £250
Area 7 £50
Area 2 etc
Area 10

FULL COST

Area 5
Area 2
Area 5
Area 20

I would need to group together Areas 5 and 2 into a region then sum the values in Column B to get the totals.

 

[xlbo]

How is an Area related to a Region ?

What is the logic that determines which areas get summed into which regions ?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[bluedragon2]

=SUMPRODUCT((A1:A1000="Area 10")*(B1:B1000))+SUMPRODUCT((A1:A1000="Area 2")*(B1:B1000))

is one way, but I think using the database (D) functions could make this work better.

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[dilworth]

There isn't any logical relationship between Area and Region-this would need to be grouped manually, however the aim would be once the Areas are matched to the regions this logic can be saved and applied whenever the source data changes.

There seems to be several tools for analysing groups of data but the data needs to be in logical tables or list formats , however as the data is exported from an application it is already formatted and changes each time the export is run and would take longer to format into a table then it would to manually sum the groups.


I was thinking of using a function that would say for Regaion A sum the values in Column B if the Area in Column A is 'Area 5' or 'Area 2'.

The sumif fucntion seems to work for one area but not more than one area.

I would welcome any more suggestions.

 

[SkipVought]

Hi,

Your biggest problem is that you are using a REPORT rather than the Source Data that this report was generated from. Maybe you have no control over that, but I would move close to heaven and earth to locate and use the data source rather than a report.

That being said, there are times when a report is all you've got to work with. In such instances, I "scrape" the report and create a proper table, using rules similar to what you stated, like "Region A should contain Area 10 and Area 2"

BUT, your job may be easier that that. I used the SUMIF function to generate this in less than 20 seconds...
[tt][highlight white]
Area 1 =SUMIF(A:A,E1,B:B)[/highlight] where E1 contains Area 1
Area 1 250
Area 2 1000
Area 3 0
Area 4 0
Area 5 95
Area 6 0
Area 7 50
Area 8 0
Area 9 50
Area 10 575
Area 11 0
Area 12 0
Area 13 0
Area 14 0
Area 15 150
[/tt]
Each of these ranges could be named, using Insert > Name > Create names in the LEFT column and then Region A becomes =Area_2+Area_10 pretty simple stuff.

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[SkipVought]

"The sumif fucntion seems to work for one area but not more than one area."

What does that mean?

What does it NOT work for?

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[xlbo]

As per Skip's post. You need to create a table of relationships between Area & Region

You can then use that to help with the summing

The table would look something like:

[tt]
Area 1 Region 1
Area 2 Region 1
Area 10 Region 1
Area 3 Region 2
Area 4 Region 2
[/tt]

Your 1st step int he process would be to add a column to your data which would be a lookup based on the above table to return a Region to each row

You would then use this new Region indicator to SUMIF against or put into a Pivot Table

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[dilworth]

Thanks for the replies to this-the problem with 'SUMIF' is that it only allows for a single value to be added to the criteria

e.g. it's possible to sumif A1:A100 is Area 1 but not if A1:A100 is Area 2 and Area 10

However, I have got round this using the 'SUMPRODUCT' function-the problem with this is that there are lots of areas to include for each region and therefore the formula would be very long and take too long to build.

The suggestion of using named ranges is a great suggestion-I'm not familiar with this function so I'm trying to work with this.

I'm not sure how to go about this as the data is set out as follows:-

COLUMN A COLUMN B

BASIC COST

Area 1 £250
Area 7 £50
Area 2 etc
Area 10

FULL COST

Area 5
Area 2
Area 5
Area 20

I created a seperate list within the same worksheet that looks something like the following:

Column H Column I

Area 2 Region A
Area 10 Region A
Area 19 Region A
Area 20 Region A
Area 1 Region B
Area 4 Region B
Area 8 Region B
Area 9 Region B
etc

I have then named the range containing Area 2, 10 19 and 20 to be called Region A.

I'm hoping this is now close to the required result but having problems with the formula-I ahve used

=SUMIF(A1:A100, Region A, B1:B100) however I get a 0 value -I'm probably going wrong with the criteria and how to refer the criteria to the named range correctly.

Any further help appreciated.

 

[xlbo]

As per my last post

your FIRST step is to use the lookup table you have created to populate Region data next to your Areas.

To do this you would use a VLOOKUP or a combination of INDEX/MATCH

your data should then look like:
[tt]
COLUMN A COLUMN B COLUMN C

BASIC COST #N/A
#N/A
Area 1 £250 Region 1
Area 7 £50 Region 1
Area 2 etc Region 1
Area 10 Region 1
#N/A
FULL COST #N/A
#N/A
Area 5 Region 2
Area 2 Region 2
Area 5 Region 2
Area 20 Region 2
[/tt]

You can then use SUMIF like:

=SUMIF($C$2:$C$1000,"Region 1",$B$2:$B$1000)
=SUMIF($C$2:$C$1000,"Region 2",$B$2:$B$1000)

OR, if you have a list of Regions in say column M, starting in M2, you can use =SUMIF($C$2:$C$1000,M2,$B$2:$B$1000

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[dilworth]

Thanks Geoff-please could you advise how the VLOOKUP formula should be written based on the Area in Coulmn A and the look-up table in Column M/N-this seemed to return 0 values last time-thanks for your help with this.

 

[xlbo]

Check out the help file for VLOOKUP for more info but in essence, if you have a value in A2 and your lookup table is in H2:I20 then your vlookup would look something like

=vlookup($A2,$H$2:$I$20,2,false)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[dilworth]

Thanks for everone's help with this.

 

[dilworth]

Please can anyone advise if it's possible to use named ranges oin the vlookup function.

 

[xlbo]

yup

=vlookup($A2,LookupRange,2,false)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question