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
 

Chaozfate

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

Hi, thanks for following up. I tried SUMPRODUCT too, unfortunately, an error message pop up say, my excel cant perform such calculation due to not enough memories.

Not sure what is the issue, i consulted with my IT, they have no idea too. So we basically gave up in designing such report.

On the other side, i tried SUMPRODUCT in a smaller data with the same pattern but it doesn't work as well. If you are interested in helping me to solve my mystery. i have upload a drafted sample file into google drive for your ease reference.

Cheers

 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

hartsie

Board Regular
Joined
May 6, 2020
Messages
62
Office Version
  1. 2016
You can do it with the sumif. It won't give you the problems that you had before.

Here it is:

 

Chaozfate

Board Regular
Joined
Mar 15, 2017
Messages
52
You can do it with the sumif. It won't give you the problems that you had before.

Here it is:

hi, you did not share workbook, i cant access it, already request for access, looking forward for your approval
 

Chaozfate

Board Regular
Joined
Mar 15, 2017
Messages
52

ADVERTISEMENT

Hi Hartsie,

I manage to achieve the report that I were expecting. Thanks to your excel guide!!! Thanks for your initiative of following up despite I gave up before.

The reason why i keep failing at SUMPRODUCT is mainly due to my sum range selection contain alphabets. I didn't know it have to be full numeric, how silly of me. Your excel provided a clear guidance towards my success. Thanks alot!!

(perhaps previously excel memories failure due to I requested the impossible)
 

hartsie

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

I manage to achieve the report that I were expecting. Thanks to your excel guide!!! Thanks for your initiative of following up despite I gave up before.

The reason why i keep failing at SUMPRODUCT is mainly due to my sum range selection contain alphabets. I didn't know it have to be full numeric, how silly of me. Your excel provided a clear guidance towards my success. Thanks alot!!

(perhaps previously excel memories failure due to I requested the impossible)
Great news! I am happy that it worked out for you.
 

hartsie

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

ADVERTISEMENT

Hi Hartsie,

I manage to achieve the report that I were expecting. Thanks to your excel guide!!! Thanks for your initiative of following up despite I gave up before.

The reason why i keep failing at SUMPRODUCT is mainly due to my sum range selection contain alphabets. I didn't know it have to be full numeric, how silly of me. Your excel provided a clear guidance towards my success. Thanks alot!!

(perhaps previously excel memories failure due to I requested the impossible)
You did see the sumifs function below the sumproduct function, right?
 

Chaozfate

Board Regular
Joined
Mar 15, 2017
Messages
52
You did see the sumifs function below the sumproduct function, right?

Just the SUMPRODUCT, doesn't have to combine with the SUMIFS, but however, when i import the formula back to my big database, the same error message "excel doesn't have enough resources to calculate", i guess SUMPRODUCT is not effective against big database, or my excel/pc specs is just too low to handle the formula calculation. Back to square one again. HAHA.

But i will consider as case closed since i am able to figure out whats my error. It is now left with the IT issue to resolve the memory issues.

Thanks again!
 

hartsie

Board Regular
Joined
May 6, 2020
Messages
62
Office Version
  1. 2016
Just the SUMPRODUCT, doesn't have to combine with the SUMIFS, but however, when i import the formula back to my big database, the same error message "excel doesn't have enough resources to calculate", i guess SUMPRODUCT is not effective against big database, or my excel/pc specs is just too low to handle the formula calculation. Back to square one again. HAHA.

But i will consider as case closed since i am able to figure out whats my error. It is now left with the IT issue to resolve the memory issues.

Thanks again!
I included the sumif in the excel sheet I provided you. SUMPRODUCT is resource intensive. SUMIF is not. You should be able to use SUMIF without issues.
 

hartsie

Board Regular
Joined
May 6, 2020
Messages
62
Office Version
  1. 2016
@Chaozfate you should look to the lower part of the excel sheet I provided. It shows the sumifs formula used for your case.
 

Watch MrExcel Video

Forum statistics

Threads
1,112,995
Messages
5,543,183
Members
410,584
Latest member
Bluefox68
Top