• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Array lookup criteria in sumifs formula

G

Gael

New Member
Hi All,

First of all, apologies for the title of the thread, but I don't have any easy way to explain what I need ;)

I've got 2 tables:
Table#1 contains raw data, which is defined with codes
Table#2 is a mapping of codes and destinations

I'm trying to build a report that aggregates the data on a destination level (there can be multiple codes assigned to a single destination).

I've tried the following:
=SUM(SUMIFS(C7:C13,B7:B13,INDEX(G7:G12,MATCH(O7,H7:H12,0))))
This doesn't work as the index/match bit returns a single value (I need an array instead)

What I would like to do is this:
=SUM(SUMIFS(C7:C13,B7:B13,{"Code1 OP","Code1 P","Code1 Wed"}))

But obviously I don't want to hard code the values in the array manually.

I've uploaded a file with an example.

Thanks in advance for your time!

Gael
 

Attachments

  • Sumifs array lookup criteria.xlsx
    10.4 KB · Views: 7
Hi Gael,

Can we just add a column to the Data Table? If we add a helper column with formula like:
=VLOOKUP(Code,CodeMappingTable,2,FALSE)
That will pull in the correct country, and then the formulas for the reports table are a few simple SUMIF calculations.
 
Gael said:
Hi All,

First of all, apologies for the title of the thread, but I don't have any easy way to explain what I need ;)

I've got 2 tables:
Table#1 contains raw data, which is defined with codes
Table#2 is a mapping of codes and destinations

I'm trying to build a report that aggregates the data on a destination level (there can be multiple codes assigned to a single destination).

I've tried the following:
=SUM(SUMIFS(C7:C13,B7:B13,INDEX(G7:G12,MATCH(O7,H7:H12,0))))
This doesn't work as the index/match bit returns a single value (I need an array instead)

What I would like to do is this:
=SUM(SUMIFS(C7:C13,B7:B13,{"Code1 OP","Code1 P","Code1 Wed"}))

But obviously I don't want to hard code the values in the array manually.

I've uploaded a file with an example.

Thanks in advance for your time!

Gael
Click to expand...
Hi,

Just onething i want to know, we can't insert any row next to table1.
I inserted on help coloumn find answer. Kindly advice on this.
 

Attachments

  • Sumifs array lookup criteria.xlsx
    10.3 KB · Views: 2
You must log in or register to reply here.
Back
Top