Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# populating value in last record based on multiple conditions

Posted on 2012-03-28
Medium Priority
281 Views
Dear experts Need a formulae
1.      which checks for every unique value in column A (variable) and
2.      at the same check for value not equal to 1 under column B,
3.       and then post 10% in column D.but
4.       IF there are multiple records for a value in column A with column B <>1,
5.      then the last record should get the posting.
6.      In addition, if there is only one occurrence of a value under column A, and column B<>1, then 10% will be populated under column D.
7.      That is no occurrence in column B=1 will get 10%.

thank you
value-in-the-last-record-with-co.xlsx
0
Question by:Excellearner
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2

LVL 43

Expert Comment

ID: 37780259
If this question is different from your previous question, can you explain why there is no value in row 11 and why there are values in rows 12, 13, 14?
0

Author Comment

ID: 37780307
ssaqibh,

thank you for the observation.

OK in my example, value should have been in row 12 and not in 11,
The reason 13 and 14 do not have values is because these are records which have 1 against them in column B. Even if the values in a13 and a14 repeat and have value 1 against them under column B, there would no value.

Which means that if any cell has value 1 in column B, then they will not have any value in column D.

Thank you
0

LVL 43

Accepted Solution

Saqib Husain, Syed earned 2000 total points
ID: 37780334
Then try

=IF(B2<>1,IF(COUNTIFS(A:A,A2,B:B,"<>1")=COUNTIFS(\$A\$1:A2,A2,\$B\$1:B2,"<>1"),10%,""),"")
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.