Return cell value base on multiple columns and rows criteria

Chaozfate

Board Regular
Joined
Mar 15, 2017
Messages
52
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
 

Some videos you may like

Excel Facts

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

hartsie

Board Regular
Joined
May 6, 2020
Messages
62
Office Version
  1. 2016
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
Joined
May 6, 2020
Messages
62
Office Version
  1. 2016
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
Joined
Mar 15, 2017
Messages
52
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?
 

hartsie

Board Regular
Joined
May 6, 2020
Messages
62
Office Version
  1. 2016

ADVERTISEMENT

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.

 

hartsie

Board Regular
Joined
May 6, 2020
Messages
62
Office Version
  1. 2016
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
Joined
May 6, 2020
Messages
62
Office Version
  1. 2016

ADVERTISEMENT

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
Joined
May 6, 2020
Messages
62
Office Version
  1. 2016
1593573458198.png
 

hartsie

Board Regular
Joined
May 6, 2020
Messages
62
Office Version
  1. 2016
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
Joined
May 6, 2020
Messages
62
Office Version
  1. 2016
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,258
Messages
5,546,818
Members
410,759
Latest member
Bufnercash
Top