# 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

#### Chaozfate

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

### 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
You can do it with the sumif. It won't give you the problems that you had before.

Here it is:

#### Chaozfate

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

#### hartsie

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

##### Board Regular

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

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
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
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
@Chaozfate you should look to the lower part of the excel sheet I provided. It shows the sumifs formula used for your case.

Replies
4
Views
113
Replies
0
Views
80
Replies
15
Views
200
Replies
3
Views
426
Replies
16
Views
662