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.
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

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.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,992
Messages
5,545,375
Members
410,679
Latest member
rolandbianco
Top