Return cell value base on multiple columns and rows criteria

[Chaozfate]

	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

 

[Chaozfate]

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

Test Book.xlsx

drive.google.com

 

[hartsie]

You can do it with the sumif. It won't give you the problems that you had before.

Here it is:

Google Sheets: Sign-in

Access Google Sheets with a personal Google account or Google Workspace account (for business use).

docs.google.com

 

[Chaozfate]

You can do it with the sumif. It won't give you the problems that you had before.

Here it is:

Google Sheets: Sign-in

Access Google Sheets with a personal Google account or Google Workspace account (for business use).

docs.google.com

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

 

[hartsie]

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

I sent you an email copy from google drive.

 

[Chaozfate]

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]

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]

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]

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]

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]

@Chaozfate you should look to the lower part of the excel sheet I provided. It shows the sumifs formula used for your case.