Sumif Criteria from List in another column

1. Sumif Criteria from List in another column

Hi, Not sure if this is possible. But, I was trying to figure out if I could make a criteria from another sheet column

For Example I have this,
=SUM(SUMIFS(tAMEX!F:F,tAMEX!C:C,{"*Comcast*","*verizon*"}, tAMEX!B:B, ">="&B3, tAMEX!B:B, "<="&D3))
I would like to take {"*Comcast*","*verizon*"} out and put those values in a column on another sheet.

For example,
=SUM(SUMIFS(tAMEX!F:F,tAMEX!C:C,Criteria!A:A, tAMEX!B:B, ">="&B3, tAMEX!B:B, "<="&D3))

Obviously that doesn't work.

Is there away to make this work? Or a way to make the first formula cleaner. For instance if I wanted to add a bunch of criterias?.

2. Re: Sumif Criteria from List in another column

Hi Dormie,

I think you are looking for Advanced Filters, that allow many criteria.

See:
for an example.

If the criteria is on the same row, it means AND
If the criteria is on different rows, it means OR
You can have as many rows of criteria as you want/need. Using stuff like "*Comcast*" is allowed as wildcard searches.

3. Re: Sumif Criteria from List in another column

Or find the section titled "Example 3. SUMPRODUCT & SUMIF" via the link below.

BSB

4. Re: Sumif Criteria from List in another column

Removed by JT

5. Re: Sumif Criteria from List in another column

Thank you for the quick responses. I'm taking a look now at them.

6. Re: Sumif Criteria from List in another column

Hi, Just wanted to give an update on this.

BSB - Thank you for link it had exactly what I need.

In case anyone finds this and is looking for the same solution

=sumproduct()
Is the solution.

=SUM(SUMIFS(tAMEX!F:F,tAMEX!C:C,{"*Comcast*","*verizon*"}, tAMEX!B:B, ">="&B3, tAMEX!B:B, "<="&D3))
Revised to:
=SUMPRODUCT(SUMIFS(tAMEX!F:F,tAMEX!C:C,Criteria!A1:A100, tAMEX!B:B, ">="&B3, tAMEX!B:B, "<="&D3))
Then list criteria in Sheet Criteria Column A

As a side note, A:A crashed my excel (365) a couple of times. So I adjusted to A1:A100 or as needed.

Certainly a much neater/cleaner way to look at it.

7. Re: Sumif Criteria from List in another column

Personally I'd avoid using entire column references such as F:F when SUMPRODUCT is involved as it's not overly efficient when referring to entire rows/columns.

My approach is usually to use dynamic named ranges that expand/contract as you add/remove data, that way the formula only ever looks at the rows used.

BSB

8. Re: Sumif Criteria from List in another column

Good tip, I'll have to look into that. How difficult is that? Do you have a quick example?

9. Re: Sumif Criteria from List in another column

Here you go. Press Ctrl+F3 to open the name manager. You'll see three named ranges, one for each column in the data. The ranges are defined via formulas that for Column A looks like:
Formula:
`Please Login or Register  to view this content.`

I've used these three named ranges in the formula (yellow cell) that resembles that your formula.

Add/remove data that matches the criteria used (green cells) and you'll see that the ranges expand/contract to match.

Note you don't have to create a named range for every column, just the ones you wish to use in formulas.

You could also achieve the same with the use of tables.

BSB

10. Re: Sumif Criteria from List in another column

BSB, That's great! I've never worked with that ranges like that before.

Thank you for the sheet, I messed around with it and it's definitely something that would work great for my use.

I'm sure I'll have a couple of additional questions!

I'll let you know what I come up with.

Thank you again!

11. Re: Sumif Criteria from List in another column

I've sent up the named range and it works great!

I do have an additional question on setting up a sumproduct/search to test each row and true of false. Not sure if I should post it as another topic or not.

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1