Sum with Multilple Criterias at Row & Column

Welcome to the forum!

What is your perceived problem with SUMPRODUCT? You could use an INDEX MATCH MATCH array formula.

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

EDIT: Please ignore the following - I have just seen the Sample Workbook, and the does not apply.

Welcome to the forum!

In future, it is much easier to help if you attach a worksheet. Pictures are not likely to get a favourable response.

Here is a suggestion.


In row C8:H8 I entered the formula =C10&C11 and copied across. This gives a concatenation of product and date. (This is a "helper row". You can hide the row, or make the text white so that it can't be seen).

Next, in row 70, I entered more "helper cells". In C70:E70 I entered Feb-17 (as a date), Orange and Third Party. These cells can now be changed as you choose to select the cross reference you want.

Finally, in C71 i entered =VLOOKUP(E70,B12:H15,MATCH(D70&C70,B8:H8,0)) This delivers the value in G14.

I hope this helps, please let me know!

Regards,

David

If this has been helpful
- Please click on the *Add Reputation button at the bottom left.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

This does what the workbook information instructs. I am mystified how it will apply for the whole project. In Q9

Formula:

Please Login or Register  to view this content.

What am I missing?

Or try

Formula:

Please Login or Register  to view this content.

Or try

Formula:

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Originally Posted by ygw0707

Hi, thanks for your suggest.. I will try to avoid by using the formula with CTRL+SHIFT+ENTER as I will need to use the formula very frequently.. and I do not wish to slow down the excel worksheet..

The Post #6 would be great for your