Hi,
I have two worksheets.In worksheet 1, Iam pulling data in BA column from worksheet2 from Y column .But here seems to be wrong data pulling by matching 4 columns which is compulsory criteria.
This is happening only in this case.
Pls find enclosed in attachment.
What is the problem?
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Pls see the data pulled in worksheet 1,it is extracting wrong data
Are you expecting SUMIFS to differentiate between tr>144 (B) and TR>144 (B)? SUMIFS isn't usually "case-sensitive", so it's treating those two values as the same. If you need it to be case sensitive then you need to switch to s SUMPRODUCT formula with EXACT or something similar
Last edited by daddylonglegs; 01-01-2018 at 09:39 AM.
Audere est facere
I did. It didn't tell me anything. I'm afraid I don't have time to through your workbook and check calculations to work out what the problem is (i.e. what data is 'wrong'). You, however, could easily explain it in words, if you were so inclined, and make it easier for those trying to help you.Originally Posted by paradise2sr
Yeah,You are right.Can you revised the same.
You have 3 variants:
tr>144 (B)
TR>144(B)
TR>144 (B)
Do you want EACH ONE to be treated as different?
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
For a revised version it's best to restrict the number of rows - this version will do a case-sensitive match for only the last criterion
=SUMPRODUCT('2'!$Y$2:$Y$51,('2'!$A$2:$A$51=B5)*('2'!$T$2:$T$51=A5)*('2'!$V$2:$V$51=V5)*EXACT('2'!$X$2:$X$51,AJ5))
Yeah sure as there are 4columns to match.
Actually I want all the criteria to be matched and pull the data accurately as I have more than 20k data like this.
As the data in sheet 1 in AJ column all 3 data are different which can be checked using Len function.Hence,plz help me in getting correct formula.
Question 1. In your posted sheet, is Daddy LL formula giving you the expected result?
Question 2. Will you please post a sample with some more examples - to show us what variations there can be?
Ok I have duly checked and it seems to work as provided by forum contributor Daddy LL in my original file.As I have never used 'Exact' function earlier.Can you plz explain about 'Exact' function.Also why there is no exact match on case sensitive matching using sumifs.
However,currently my problem is solved on this topic.
Thanx for your kind help.
Last edited by paradise2sr; 01-02-2018 at 07:49 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks