Summing COlumn Based On Criteria In Named Range

[Abulafia]

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.

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.

 

[Artorius]

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]

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]

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

 

[Abulafia]

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]

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]

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]

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.

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]

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]

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.