Return cell value base on multiple columns and rows criteria

Chaozfate

Board Regular
 A B C D 1 Product A 2 Gross Profit 3 Balance 1/6/2020 30/6/2020 USD 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

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

hartsie

Board Regular
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?

hartsie

Board Regular
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.

Chaozfate

Board Regular
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/2020 30/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?

hartsie

Board Regular

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/2020 30/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.

hartsie

Board Regular
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.

hartsie

Board Regular

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:

!

hartsie

Board Regular
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))

hartsie

Board Regular
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/2020 30/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.

Replies
4
Views
140
Replies
0
Views
82
Replies
15
Views
206
Replies
3
Views
448
Replies
16
Views
766