Summing COlumn Based On Criteria In Named Range

Abulafia

Board Regular
Joined
Jun 15, 2004
Messages
66
I'm having a problem calculating something based on some criteria held in an array.

For example, I have an named range called Expenses which holds the following values: 10050, 20050, and 20000.

What I want to do is put the sum total of column a where the Expenses range is met, summing up column B.



Code:
COLUMN A | COLUMN B 
   10000    |      10     
   10050    |      20        
   20000    |      30
   20050   |       40
   20075   |       50

I've had a mess around with SUMIF, SUMPRODUCT, and IF(SUM() array forumulas but have had no success thus far.
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Artorius

Board Regular
Joined
Apr 18, 2006
Messages
176
Do you mean you want to SUM if Expenses in Column A are within set ranges such as :-

Between 10,000 to 15,000
Between 15,001 to 20,000
Between 20,001 to 25,000

etc etc?

If so, I'd be tempted to rope in the LOOKUP function. Please provide details of the "Expenses Range".
 

Abulafia

Board Regular
Joined
Jun 15, 2004
Messages
66
Do you mean you want to SUM if Expenses in Column A are within set ranges such as :-

Between 10,000 to 15,000
Between 15,001 to 20,000
Between 20,001 to 25,000

etc etc?

No. If they are explicitly those listed in the named range I want to SUM them. It's just a list of so many codes.
 

Artorius

Board Regular
Joined
Apr 18, 2006
Messages
176
I see .... can you not use SUBTOTAL by change in Expenses (Column A) or does it need to be a formula?
 

Abulafia

Board Regular
Joined
Jun 15, 2004
Messages
66

ADVERTISEMENT

I see .... can you not use SUBTOTAL by change in Expenses (Column A) or does it need to be a formula?

No, I can't SUBTOTAL.

There is one sheet with all this downloaded data, like the example given above. There is one sheet in which I've put the range and named it Expenses. On a third sheet there is all manner of summaries, and in one cell I want to total up all of one column where the values in another column match the Expenses range.
 

Artorius

Board Regular
Joined
Apr 18, 2006
Messages
176
On the summary sheet, say you have in cell A5, the value 10500

In Expenses on Sheet1, you have in column A all the expense codes including 10500, column B is all the values you want to SUM.

in cell B5, this will be your total ......

=SUMIF(Sheet1!A:A,A5,Sheet1!B:B)
 

Artorius

Board Regular
Joined
Apr 18, 2006
Messages
176

ADVERTISEMENT

To explain ....

Sheet1!A:A is the range of Expense Codes

A5 contains an Expense Code such as 10050

So, IF Column A in worksheet called "Sheet1" contains the value in A5 in your summary worksheet then SUM the values in COLUMN B in worksheet called "Sheet1"

Hope that makes it a little clearer.
 

Abulafia

Board Regular
Joined
Jun 15, 2004
Messages
66
On the summary sheet, say you have in cell A5, the value 10500

In Expenses on Sheet1, you have in column A all the expense codes including 10500, column B is all the values you want to SUM.

in cell B5, this will be your total ......

=SUMIF(Sheet1!A:A,A5,Sheet1!B:B)

Sorry, Artorius but you seem to have missed my point. I want the sum of ALL in the one cell.

Code:
COLUMN A    | COLUMN B 
   10000    |      10      
   10050    |      20        
   20000    |      30 
   20050    |      40 
   20075    |      50

So, when the range includes 10050, 20050, and 20000, I want the formula to return 90, being 20 + 40 + 30

As I said previously, I've tried SUMIF and array functions.

Things like:

=SUMIF(A1:A5, Expenses, B1:B5)
={SUMIF(A1:A5, Expenses, B1:B5)}
={SUM(IF(A1:A5=Expenses,B1:B5))}
=SUMPRODUCT((A1:A5=Expenses)*(B1:B5))
={SUMPRODUCT((A1:A5=Expenses)*(B1:B5))}
 

Artorius

Board Regular
Joined
Apr 18, 2006
Messages
176
Yes, I missed the point completely ... sorry :)

If "EXPENSES" is the codes only, try ....

=SUMIF(EXPENSES,">0",B:B)

is that what you want?
 

Abulafia

Board Regular
Joined
Jun 15, 2004
Messages
66
Yes, I missed the point completely ... sorry :)

If "EXPENSES" is the codes only, try ....

=SUMIF(EXPENSES,">0",B:B)

is that what you want?

Sorry, no that's not what I want either. Since that formula is going to look at the array where the values are greater than 0. Everything within the expenses array is greater than 0.

I just decided to do a workaround by putting in a hidden column with a formula returning 0 or 1 depending on whether a VLOOKUP was a success and then used a SUMIF to calculate the column if the lookup returned a 1.

Thanks for your efforts though.
 

Forum statistics

Threads
1,141,027
Messages
5,703,818
Members
421,318
Latest member
cg_cartoonexcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top