# Extract values based on two (2) criteria

1. ## Extract values based on two (2) criteria

Would like to extract values based on two (2) criteria. Criteria are in B1 and C1. Raw data is in Sales Tab and expected outcome is in Extract Tab. See attached Excel file. If South and 2010, 2011 are selected for B1 and C1, extract values should be.

``Please Login or Register  to view this content.``

Thanks

2. ## Re: Extract values based on two (2) criteria

perfect example of when to use pivot table and slicer

Ps if you put the data into a table like i have in the example it will even auto update the pivot table data range

3. ## Re: Extract values based on two (2) criteria

humding, can you explain how you did that?

4. ## Re: Extract values based on two (2) criteria

certainly

i took the data converted it into a table (optional step) and inserted into a pivot table (you can choose a new sheet or place in the same sheet like i have in the example)

For the fields used
Report filter - dragged in "Region"
Row filter - dragged in "Month"
Column filter - dragged in "Year"
Values field - "Sales" "Unit price" and "Profit" were all dragged in (in that order)

if you do not know how to do this, here is a link for step by step instructions on inserting a pivot table and what the fields look like
http://www.excel-easy.com/data-analy...ot-tables.html

some additional (and optional) formating of numbers were applied to make it look prettier

after the pivot table was setup i inserted two slicers
one for "region" and one for "year"

step by step instructions (with videos) on how to do this can be found here
http://www.contextures.com/excelpivottableslicers.html

5. ## Re: Extract values based on two (2) criteria

Cool, thank you

6. ## Re: Extract values based on two (2) criteria

humdingaling: neat and cool solution! a formula approach will be great. Can you pls use a formula approach to achieve the same outcome?

Thanks

7. ## Re: Extract values based on two (2) criteria

see attached

using combination of sumifs and basic logic based on the 2nd validation cell

not exactly what you asked for but quite similar
if you want exactly it can be done...the logic of what is blank is what isnt just would take a lot longer to think about

9. ## Re: Extract values based on two (2) criteria

some tinkering I've made it exactly as in your example....minus the formatting for unit price....that you can do with conditional formatting

however as you can see
the coding behind this is much more complicated and finicky than pivot table (and i consider pivot tables finicky already)
its much harder to maintain
its much harder to fix when something goes wrong

10. ## Re: Extract values based on two (2) criteria

humdingaling: great solution. I formula works great. However, when I changed Sales!\$A:\$A, Sales!\$B:\$B and Sales!\$C:\$C to Sales!\$A\$2:\$A\$37, Sales!\$B\$2:\$B\$37 AND Sales!\$C\$2:\$C\$37, i am getting value error.

Modified and getting value error code: in column J
``Please Login or Register  to view this content.``
Thanks

11. ## Re: Extract values based on two (2) criteria

=IFERROR(SUMIFS(INDEX(Sales!\$D:\$F,,MATCH(B\$3,Sales!\$D\$1:\$F\$1,0)),Sales!\$B:\$B,B\$4,Sales!\$A:\$A,\$A5,Sales!\$C:\$C,\$B\$1),"")

Copied down and across

12. ## Re: Extract values based on two (2) criteria

Ace_XL: works but i am still getting no value when I changed Sales!\$B:\$B,B\$4,Sales!\$A:\$A,\$A5,Sales!\$C:\$C to Sales!\$B\$2:\$B\$37,B\$4,Sales!\$A\$2:\$A\$37,\$A5,Sales!\$C\$2:\$C\$37. Instead of Sales!\$B:\$B,B\$4,Sales!\$A:\$A,\$A5,Sales!\$C:\$C, i would like it to readSales!\$B\$2:\$B\$37,B\$4,Sales!\$A\$2:\$A\$37,\$A5,Sales!\$C\$2:\$C\$37. Reason being that my original data point does not start at A1:F1.

``Please Login or Register  to view this content.``
Thanks

14. ## Re: Extract values based on two (2) criteria

You need the arrays to be consistent. Hence

=IFERROR(SUMIFS(INDEX(Sales!\$D\$2:\$F\$37,,MATCH(B\$3,Sales!\$D\$1:\$F\$1,0)),Sales!\$B\$2:\$B\$37,B\$4,Sales!\$A\$2:\$A\$37,\$A5,Sales!\$C\$2:\$C\$37,\$B\$1),"")

15. ## Re: Extract values based on two (2) criteria

I have added the adjusted Excel file to reflect where the actual data start (A9 instead of A1). Would like the formula not to go from Sales!\$A:\$A. Only go from A10 to the last cell with value.

Thanks

16. ## Re: Extract values based on two (2) criteria

Ace_XL: works like charm! Outstanding work! What of formula to go from C5:F5? Thanks friend

17. ## Re: Extract values based on two (2) criteria

Ace_XL: works like a charm! Outstanding work! What of formula to go from C5:G5?

Thanks friend.

18. ## Re: Extract values based on two (2) criteria

Just copy the formula in B5 down and across

It will change column reference and pull up correct answers.

See attached based on your last sample sheet

19. ## Re: Extract values based on two (2) criteria

Outstanding solution, my friend! Works like a charm.

Thanks a million

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