IF and ProductIF Functions

Megha1484

New Member
Joined
Nov 10, 2018
Messages
17
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
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,619
Office Version
  1. 365
Platform
  1. Windows
Try:
This is an array formula and must be entered with CTRL-SHIFT-ENTER.
Excel Workbook
ABCDE
1Original Table
2P#CalcMyProduct#1MyProduct#2CorrectProduct
39611101
41231100
54561100
67890000
71231100
84560000
97891100
106780000
111230000
12
13
14
15Desired Table
16P#CorrectProduct
179611
181230
194560
207890
216780
Sheet
 

Megha1484

New Member
Joined
Nov 10, 2018
Messages
17
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.





Original Table
P#CalcMyProduct#1MyProduct#2CorrectProduct
9611101
1231100
4561100
7890000
1231100
4560000
7891100
6780000
1230000
<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> </tbody>
 

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,619
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Nov 10, 2018
Messages
17
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,048
Messages
5,526,472
Members
409,701
Latest member
nitmani

This Week's Hot Topics

Top