# 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

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

#### 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
6
Views
49
Replies
9
Views
72
Replies
1
Views
91
Replies
3
Views
126
Replies
3
Views
36