IF and ProductIF Functions

[Megha1484]

Hi,

I need help with this simple calculation in Excel that I am having difficulty with. I have tried couple of formulas so far and nothing is giving me the correct answer. The formulas are displayed in "MyProduct#1" and "MyProduct#2" columns.


So the "Original table" is what I am working with and the "Desired Table"(second table below the highlighted table) is what I am trying to accomplish.

The original table displays bunch of numbers and some of them are duplicates (highlighted in different colors), but their calc values are different.
I want to look for a match in Col A and, if found one multiply their calc values and display the final result in the "Correct Product" column.


For ex.. 123 is displayed 3 times but its calc value is different all three times, I want to look for corresponding calc values and multiply them.
so the end result for 123 should be 1*1*0 = 0 and that is what I want displayed in rows 4, 7, and 11.


I tried to using the Product(IF) and IF statements in Columns C and D, but none of them are displaying the correct results for me. The desired result is displayed in COl E.


Then I would like to come up with a table (Desired Table)that displays everything from "original table" with correct product and filters out duplicates.

Any help would be greatly appreciated.
Thanks,

Here is the link to the excel file.

https://app.box.com/s/kp25drdrscz6x96298x3vf9gaqogpqnt

 

[AhoyNC]

Try:
This is an array formula and must be entered with CTRL-SHIFT-ENTER.

Excel Workbook
	A	B	C	D	E
1	Original Table
2	P#	Calc	MyProduct#1	MyProduct#2	CorrectProduct
3	961	1	1	0	1
4	123	1	1	0	0
5	456	1	1	0	0
6	789	0	0	0	0
7	123	1	1	0	0
8	456	0	0	0	0
9	789	1	1	0	0
10	678	0	0	0	0
11	123	0	0	0	0
12
13
14
15	Desired Table
16	P#	CorrectProduct
17	961	1
18	123	0
19	456	0
20	789	0
21	678	0
Sheet

 

[Megha1484]

Thanks for getting back to me! I Really appreciate your help. I should have mentioned it in my post that the Desired table does not exist. I would like to search for the duplicates in the Original Table itself.


The formula that I am using in my spreadsheet is =PRODUCT(IF($A$3:$A$11=A3,$B$3:$B$11)). I am looking for the duplicates in the Col A of Original Table and displaying results in MyProduct#2 (highlighted in red), but it is displaying 0s for all rows, which isn't correct.

What I am hoping to do is search for duplicates in Original table col A, multiply all Calc values and display the results in the next column.


Next step is to create a separate table (Desired Table) which would get rid of all duplicates and display the P# only once with correct product next to it.

I did use your suggested array formula and its giving me the same result, all 0s, not sure what I am doing wrong.





[TABLE="width: 442"]
<colgroup><col width="102" style="width: 77pt; mso-width-source: userset; mso-width-alt: 3640;"> <col width="121" style="width: 91pt; mso-width-source: userset; mso-width-alt: 4295;"> <col width="109" style="width: 82pt; mso-width-source: userset; mso-width-alt: 3868;"> <col width="130" style="width: 98pt; mso-width-source: userset; mso-width-alt: 4636;"> <col width="125" style="width: 94pt; mso-width-source: userset; mso-width-alt: 4437;"> <tbody>[TR]
[TD="width: 102, bgcolor: transparent"]Original Table[/TD]
[TD="width: 121, bgcolor: transparent"] [/TD]
[TD="width: 109, bgcolor: transparent"] [/TD]
[TD="width: 130, bgcolor: red"] [/TD]
[TD="width: 125, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]P#[/TD]
[TD="bgcolor: transparent"]Calc[/TD]
[TD="bgcolor: transparent"]MyProduct#1[/TD]
[TD="bgcolor: red"]MyProduct#2[/TD]
[TD="bgcolor: transparent"]CorrectProduct[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]961[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: red"]0[/TD]
[TD="bgcolor: transparent"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: yellow"]123[/TD]
[TD="bgcolor: yellow"]1[/TD]
[TD="bgcolor: yellow"]1[/TD]
[TD="bgcolor: red"]0[/TD]
[TD="bgcolor: yellow"]0
[/TD]
[/TR]
[TR]
[TD="bgcolor: #92D050"]456[/TD]
[TD="bgcolor: #92D050"]1[/TD]
[TD="bgcolor: #92D050"]1[/TD]
[TD="bgcolor: red"]0[/TD]
[TD="bgcolor: #92D050"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: #00B0F0"]789[/TD]
[TD="bgcolor: #00B0F0"]0[/TD]
[TD="bgcolor: #00B0F0"]0[/TD]
[TD="bgcolor: red"]0[/TD]
[TD="bgcolor: #00B0F0"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: yellow"]123[/TD]
[TD="bgcolor: yellow"]1[/TD]
[TD="bgcolor: yellow"]1[/TD]
[TD="bgcolor: red"]0[/TD]
[TD="bgcolor: yellow"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: #92D050"]456[/TD]
[TD="bgcolor: #92D050"]0[/TD]
[TD="bgcolor: #92D050"]0[/TD]
[TD="bgcolor: red"]0[/TD]
[TD="bgcolor: #92D050"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: #00B0F0"]789[/TD]
[TD="bgcolor: #00B0F0"]1[/TD]
[TD="bgcolor: #00B0F0"]1[/TD]
[TD="bgcolor: red"]0[/TD]
[TD="bgcolor: #00B0F0"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]678[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: red"]0[/TD]
[TD="bgcolor: transparent"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: yellow"]123[/TD]
[TD="bgcolor: yellow"]0[/TD]
[TD="bgcolor: yellow"]0[/TD]
[TD="bgcolor: red"]0[/TD]
[TD="bgcolor: yellow"]0[/TD]
[/TR]
</tbody>[/TABLE]

 

[AhoyNC]

What should the correct value be in your red column and how did you (or would you get it)?
What is MyProduct#1, MyProduct#2 and CorrectProduct?

 

[Megha1484]

Thanks for getting back to me and apologies for late response.


So, MYProduct#1 and MyProduct#2 are both mutiplications used in different ways.
MyProduct#1 =IF(A:A=A3,B3*B:B)
MyProdcut#2 =PRODUCT(IF($A$3:$A$11=A3,$B$3:$B$11))

CorrectProduct is what the correct answer should be.But both of the formulas mentioned above give me the wrong results.



Myabe IF and Product is not the way to go for this problem.

Here is what I am trying to do: I want to look for duplicates in #P , if found a match then either mutiply the corresponding values in Calc OR take the lowest value of the two and get rid of the other duplicate row.
so, for P# 456 = 1*0 = 0 OR it simply pick the lowest of the two P#456, which is 0 and eliminates the other row(P#456=1).

but if no duplicate found then leave the values as is. So for, P#961 it should be 1.

I hope this makes sense. Now that I think about it the second option sounds better than the first one. FInd duplicate, pick the lowest of two and eliminate the other one, and, if no match found then leave the row as is.