Compound If Statement

[rsomist]

I have a worksheet that I'm trying to get two IF functions developed based on criteria in other columns. I have been playing around with compounding the IF function with the Frequency and Sumproduct functions and keep coming up wrong and was looking to see if I could get some additional help.

Here's what I'm trying to accomplish and will attach file.

In column H - If the G column has a value of "I", then for every same value range in column F, I need a sum of column E filtering out any unique values from column B. In cell J2 I have a formula that shows all of the cells needed to get the answer - I'm just not sure how to get that in a function.

I also need a function in column I that says - If the G column has a value of "O", then for every same value range in column F, sum the values in column E filtering out any unique values from column A. In cell J20, I have another formula that shows all of the cells that are being added up to get the expected answer.

Thank you!

 

[NARAYANK991]

Hi ,

See if this is correct ; there is a difference between what you have shown as the expected results and what I am getting.

Narayan

 

[bosco_yip]

Another option without helper

In H2, copied down :

=SUMPRODUCT((F$2:F$73=F2)/COUNTIFS(G$2:G$73,G$2:G$73,F$2:F$73,F$2:F$73,E$2:E$73,E$2:E$73,IF(G2="I",B$2:B$73,A$2:A$73),IF(G2="I",B$2:B$73,A$2:A$73)),E$2:E$73)

Regards
Bosco