Index/Match or sumproduct with concatenate

Hi,

Can anyone with help with a formula please? I have in the past used sumproduct however I need to know if it is possible to concatenate the data first?

On the attached file I have two sheets “Summary for slides” and “Sales by Product”. The sales by product sheet can not change or else I would have added a column and concatenated the data here.

Its there any way in which I can pull the data from the sales by product sheet where it would look up cell F4 on the “summary for slides” sheet and then match it with columns E and G on the “Sales by product” sheet and return the answer in column P

Any suggestions?

Paul

You have multiple products with the same product name and spacer (e.g. there are 5 Product 1 Aluminium on the 'Sales by product' sheet) - are you looking to return a sum of all of their sales, or will there be a unique match in your real data?

Hi ,

I have fix the formula for your file but cannot attached or dont know to attached the file in this forum.

Please provide your email id to mail the file.


Regards
Paresh

Originally Posted by pauldaddyadams

Hi,

Can anyone with help with a formula please? I have in the past used sumproduct however I need to know if it is possible to concatenate the data first?

On the attached file I have two sheets “Summary for slides” and “Sales by Product”. The sales by product sheet can not change or else I would have added a column and concatenated the data here.

Its there any way in which I can pull the data from the sales by product sheet where it would look up cell F4 on the “summary for slides” sheet and then match it with columns E and G on the “Sales by product” sheet and return the answer in column P

Any suggestions?

Paul

Hi, I am looking to return the sum of all these matching products.

If you click "go advanced" rather than post quick reply, scroll down to manage attachements and you should be able to upload the file from there.

How to attached the file in this forum in reply.......?

OK, try this:

=SUMPRODUCT('Sales by Product'!$P$6:$P$75,--('Sales by Product'!$E$6:$E$75=D4),--('Sales by Product'!$G$6:$G$75=E4))

Hi that formula worked! I have never seen it written like that, what does the "--" mean?

Here is the file attached for your reference

Regards
paresh

Hope the file attached will solve ur purpose....

The -- converts a Boolean value (True or False) into its equivilent numeric value (1 or 0, respectively)

So --(5>10) = 0, while --(5<10) = 1

It's one of the many things this forum has taught me - I was puzzled by it as well when I first started posting here.

Duplicate post, sorry.

@Andrew thanks for explaining. Although I dont fully understand it I will 100% look into it later!

@Pareshvm -your formula didnt work as it only returned the first instance rather than the sum of all products.

this can be done by simple formula of SUMIFS :-

=SUMIFS('Sales by Product'!P:P,'Sales by Product'!E:E,'Summary for Slides'!D4,'Sales by Product'!G:G,'Summary for Slides'!E4)