Summing COlumn Based On Criteria In Named Range

Abulafia

Board Regular
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
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
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
I see .... can you not use SUBTOTAL by change in Expenses (Column A) or does it need to be a formula?

Abulafia

Board Regular

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
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

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
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
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
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.

Replies
3
Views
499
Replies
2
Views
183
Replies
1
Views
436
Replies
3
Views
375
Replies
6
Views
837

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.

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

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