Return cell value base on multiple columns and rows criteria

Chaozfate

Board Regular
Joined
Mar 15, 2017
Messages
71
ABCD
1Product A
2Gross Profit
3Balance1/6/202030/6/2020USD 1,000

Dear All,

**I tried search thru the whole forum but very unlikely any formula provided before match my request. Hence i decided to post a new thread.

Objective: The above shows a partial extraction from my big work book, I wish to retrieve the value of USD1,000 in another sheet tab based on the criteria in column A3, B3, C3, and row D1, D2.

Would be very much appreciated if anyone could advise and assist to draft if there is a formula exist for retrieving the value on D3 based on multiple criteria at respective columns and rows.

Thanks and regards,
Fate
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Have you tried using a concatenation of the search values to create a unique string, and performing a Vlookup from that? Otherwise, why couldn't you use the SUMIFS or SUMPRODUCT?
 
Upvote 0
Have you tried using a concatenation of the search values to create a unique string, and performing a Vlookup from that? Otherwise, why couldn't you use the SUMIFS or SUMPRODUCT?
So, it would be something like "SUMIFS(Sum range, criteria range 1, criteria 1, cr2, c2, cr3, c3)" Or "SUMPRODUCT((A:A=A3)*(B:B=B3)*(C:C=C3)*(D:D))" This is assuming that A, B, C column ranges are on the source sheet.
 
Upvote 0
Have you tried using a concatenation of the search values to create a unique string, and performing a Vlookup from that? Otherwise, why couldn't you use the SUMIFS or SUMPRODUCT?
hi, thanks for your reply, i tried everything but it seems like my knowledge is very limited

Perhaps my data example is not that clear. Just too add on, i will be having a lot of products on the same month, and a lot of subject under the products. ie: for the month of June, i will be having product A, B, C, and under each product, i will be having subject such as, Cost, Investment 1, Investment 2, Bank Charges and etc.

I wish to perform the search based on the following example:

1/6/202030/6/2020
Product A
Cost"search value based on the above date and return the cost of Product A"
Investment 1
Investment 2

so i tried vlookup, which can only return value based on a single row criteria
i did tried sumifs, but doesnt seems fit into extracting info from row and column at the same time
sumproduct, tried too, but doesnt work for me too.

do you mind to share your ideas if you have any?
 
Upvote 0
hi, thanks for your reply, i tried everything but it seems like my knowledge is very limited

Perhaps my data example is not that clear. Just too add on, i will be having a lot of products on the same month, and a lot of subject under the products. ie: for the month of June, i will be having product A, B, C, and under each product, i will be having subject such as, Cost, Investment 1, Investment 2, Bank Charges and etc.

I wish to perform the search based on the following example:

1/6/202030/6/2020
Product A
Cost"search value based on the above date and return the cost of Product A"
Investment 1
Investment 2

so i tried vlookup, which can only return value based on a single row criteria
i did tried sumifs, but doesnt seems fit into extracting info from row and column at the same time
sumproduct, tried too, but doesnt work for me too.

do you mind to share your ideas if you have any?
So I would try the SUMIFS because it is the most efficient of the SUMPRODUCT and SUMIFS. You can see my example provided.

I found this link that might explain SUMIFS better. I am not sure if sharing links is permitted... so please forgive me, the new user, if I have violated a rule.

 
Upvote 0
So I would try the SUMIFS because it is the most efficient of the SUMPRODUCT and SUMIFS. You can see my example provided.

I found this link that might explain SUMIFS better. I am not sure if sharing links is permitted... so please forgive me, the new user, if I have violated a rule.

So if you are trying to use dates as a criteria for SUMIF, you can simply say the cell should equal the date. So, lets say you want to have criteria in the sum if where you want the date to be equal to or greater than a cell, we will say the criteria in cell C3 for example, you would just enter as a criteria "="&C3 in the criteria.
 
Upvote 0
So I would try the SUMIFS because it is the most efficient of the SUMPRODUCT and SUMIFS. You can see my example provided.

I found this link that might explain SUMIFS better. I am not sure if sharing links is permitted... so please forgive me, the new user, if I have violated a rule.

SO - also, you can use column and row criteria. You will want to anchor your criteria. So, for your header, you will want to place a "$" symbol before the row number. So, if your date is in C1, you want to be sure your sumifs is using C$1 as the referenced cell.

See the website below for using anchors:

!
 
Upvote 0
1593573458198.png
 
Upvote 0
For this, you might want to use SUMPRODUCT. Please notice that I am referencing sheet 2 within the same sheet. Therefore, you will see that the sheet name is excluded. However, you will want to follow this format SUMPRODUCT((Array 1 = Criteria 1)*(Array 2 = Criteria 2)*(Sum Array))
 
Upvote 0
hi, thanks for your reply, i tried everything but it seems like my knowledge is very limited

Perhaps my data example is not that clear. Just too add on, i will be having a lot of products on the same month, and a lot of subject under the products. ie: for the month of June, i will be having product A, B, C, and under each product, i will be having subject such as, Cost, Investment 1, Investment 2, Bank Charges and etc.

I wish to perform the search based on the following example:

1/6/202030/6/2020
Product A
Cost"search value based on the above date and return the cost of Product A"
Investment 1
Investment 2

so i tried vlookup, which can only return value based on a single row criteria
i did tried sumifs, but doesnt seems fit into extracting info from row and column at the same time
sumproduct, tried too, but doesnt work for me too.

do you mind to share your ideas if you have any?
We are so close. Please let me know if the image and explanation above makes sense. I know that we can get you to where you need to be.
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,956
Members
448,535
Latest member
alrossman

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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