Urgent Excel Array Formula Help Please

[Thorny]

I am trying to sum rows of data based on certain criteria, this criteria is contained in each row, I will illustrate my problem with a simplified example:

2400 IF1
2500 IF2
2600 IF1

The result I would like to achieve is for the rows containing IF1 to summed giving 5000 and for IF2 to give the result 2500.

I hope this is clear, please someone help as I am getting desperate

 

[harlison]

Thorny:

Use the SUMIF function: =SUMIF(B1:B3,"IF1",A1:A3)

A B

1 2400 IF1

2 2500 IF2

3 2600 IF1

4 =SUMIF(B1:B3,"IF1",A1:A3)

5 =SUMIF(B1:B3,"IF2",A1:A3)

 

[acron]

Using harlisons layout, an array formula would be

{=SUM(IF($B$2:$B$4="IF1",$A$2:$A$4,0))}

to get the sum of values where IF1 is the code.

The formula is entered without the brace brackets, but using Crtl-Shift Enter

AC

 

[bkpchs237]

Thorny,

Try these on for size:
For IF1 totals: =SUM(($B$1:$B$3="IF1&quot*($A$1:$A$3))
For IF2 totals: =SUM(($B$1:$B$3="IF2&quot*($A$1:$A$3))

Where b1:b3 is the range with the IF1 and IF2 values and a1:a3 is the range with the values to be summed.
Again pressing ctrl+sh+enter when entering the formulas.

Hope this helps.

 

[AngO]

You could also sort by the column which contains the IF1 IF2 information, and turn on subtotals.

Subtotals are located in the Data Menu/Subtotals.

AngO

 

[Thorny]

Thanks guys for you solutions but, here is my problem, I do not have the luxury of using the actual in the cells eg, IF1, so I cannot use the Sumif function. I need some way of grouping all the "IF1", "IF2" etc together and summing the each group.

 

[dsi]

Have you considered using a pivot table? They automatically adjust based on the contents of your data. You can also include totals and subtotals.

 

[bkpchs237]

Thorny,

Your statement "I do not have the luxury of using the actual in the cells eg, IF1, so I cannot use the Sumif function." makes little sense here unless I understand the problem incorrectly. Your initial post states an example:
2400 IF1
2500 IF2
2600 IF1
Are these values within the same cell? or are the values 2400 and IF1 in different cells? Our answers have all "assumed" that they are in different cells (i.e. 2400, 2500 and 2600 are in column A and IF1, IF2 and IF1 are in column B). If this is not the case please advise.

Your contention "I need some way of grouping all the "IF1", "IF2" etc together and summing the each group." is unclear given the answers you've received. In either case our solutions will work whether your items are grouped together or not. A little more explanation, with some sort of specifics and perhaps more sample data, would be helpful to those of us trying to assist you.

However, if you need to group the items, use the Data, Sort command and sort your data as needed. Then you can either employ a pivot table as dsi has suggested, or you can use the Data, Subtotals, or Data, Group and Outline commands.

Hope this helps.