LinkBack URL
About LinkBacks
I need to calculate the measure value based on 3 conditions in the excel sheet. If Type = Machine and MonthNumber = 1 and Label = LabelD then show the measure value. How can I write such formula ?
HTML Code:
I need to calculate the measure value based on 3 conditions in the excel sheet. If Type = Machine and MonthNumber = 1 and Label = LabelD then show the measure value. How can I write such formula ?
HTML Code:
Last edited by donny007; 01-17-2019 at 08:47 PM.
so none of them fulfills the condition for this eg?
=IF(AND(A2="machine",B2=10,C2="labeld"),D2,"")
Thanks, if you have clicked on the * and added our rep.
If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".
"Contentment is not the fulfillment of what you want, but the realization of what you already have."
Tips & Tutorials I Compiled | How to Get Quick & Good Answers
Sorry, I just edited my post again. It should be monthnumber = 1. Your formula doesn't capture the value within the range of values ?
Last edited by donny007; 01-17-2019 at 08:53 PM.
so your goal is to sum up the measures fulfilling those conditions? use SUMIFS instead.
=SUMIFS(D:D,A:A,"machine",B:B,1,C:C,"labeld")
do upload an excel sample so that we do not have to manually key in your data to do a testing.
input the desired results so that we don't have to second-guess if what we are doing is correct or not. you may look at my signature to upload an eg that is easier to understand.
the upload attachment must be done by going to Go Advanced. click on Manage Attachments. Choose file, upload and close the window.
if i really guessed it wrongly, please upload something according to my recommendations. try to use the solution i have given and we'll help to advise what went wrong
I have uploaded the excel file to my original post. I don't want the sum. I just want the measure value based on the conditions.
Last edited by donny007; 01-17-2019 at 08:53 PM.
Can we write something like this ? But it doesn't return any value.
=INDEX($D$2:$D$60,MATCH(3,($B$2:$B$60="1")*($A$2:$A$60="Machine")*($C$2:$C$60="LabelD"),0))
Change the 3 to 1 just after the MATCH(, and use Ctrl-Shift-Enter to commit the formula, as it is an array formula.
Hope this helps.
Pete
or... use this with enter, as normal:
=INDEX($D$2:$D$60,MATCH(1,index(($B$2:$B$60="1")*($A$2:$A$60="Machine")*($C$2:$C$60="LabelD"),0),0))
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
Another option change your formula to this and this is array formula, so you need to press all together CTRL+ALT+ENTER button, when succes curly bracket will showed up
=INDEX($D$2:$D$60,MATCH(1,($B$2:$B$60="1")*($A$2:$A$60="Machine")*($C$2:$C$60="LabelD"),0))
Last edited by azumi; 01-17-2019 at 09:16 PM.
Thanks guys! Your solutions worked perfectly.
As an extension to this
I would like to add a filter(like a checkbox or a dropdown list)in the report. Column E (ClientName) should be the filter so the end users can select the value and filter the entire report.
For example: If the user selects 'AK', the report returns data only related to AK. If the user selects 'BK', the report should return the data only related to BK.
How can I do this ?
SUMIFS is better by far for this whole thing, not INDEX-MATCH. Does this do what you wanted?
Thank you Glenn! Could you tell me how did you create the drop down list filter ?
As Glenn is off-line, I'll answer for him. You can see that Glenn has added a list of names in column P, and you can see on Name Manager that this has been given the name "List".
Then on cell G1 if you click on Data | Data Validation you can see the option for List has been selected in the Allow box, and the Source for this is defined as =List (i.e. the named range defined above). This allows you to make selections from the drop-down which are restricted to allow only those values.
Hope this helps.
Pete
Thanks, Pete.... I am far away from home... with limited www access, as power cuts are not infrequent here.
But at 32C and sunny, it's better than an Irish (or Warrington) winter...
Thanks Pete!
One more question, how can I filter the data in the sheet when I select a value from the drop down list ? For example If I pick the value from the drop down list as Q, then columns A,B,C,D,E should only show the data related to Q. If I pick the value from the drop down list as T, then columns A,B,C,D,E should only show the data related to T.
I don't know of a way to do that in situ on the same sheet using a formula, but you could have a filtered list on another sheet which is governed by the drop-down (or it could be in a different area of the same sheet). Would this be acceptable?
Pete
So, which do you want to do... select "Q" from the dropdown, or from a filter?
Select Q from the dropdown and see the 5 columns in the detail report changing.
OK. You can do that... but the results will be on another sheet, or elsewhere on the same sheet. Your choice...
Glenn: The results will be on another sheet.
Thanks! I love Irish music by the way.
Last edited by donny007; 01-18-2019 at 11:34 AM.
I've set this up for you in the attached file. Rather than embed the choices with the SUMIFS formula, I've given you 4 drop-downs (yellow cells on Sheet2) which enable you to choose the options, or you can leave them blank to mean "All" values. These drop-downs are fed from the named ranges in columns K to O, with the names given on row 1.
I have used a helper column on Sheet1 (main data sheet), with this formula in F2:
=IF(AND(OR(E2=Sheet2!$E$1,Sheet2!$E$1=""),OR(C2=Sheet2!$C$1,Sheet2!$C$1=""),OR(B2=Sheet2!$B$1&"",Sheet2!$B$1=""),OR(A2=Sheet2!$A$1,Sheet2!$A$1="")),MAX(F$1:F1)+1,"-")
and this can be copied down beyond your data to accommodate more data being added - the hyphens help to show where the formula is active. The formula identifies those records which match the options chosen, and allocates a unique sequential number to each.
Then I have used this formula in A4 of Sheet2:
=IFERROR(INDEX(Sheet1!A:A,MATCH(ROWS($1:1),Sheet1!$F:$F,0)),"")
which returns data from column A of Sheet1. This can be copied across to column E, and then copied down as far as you like (I've copied to row 15 in this demonstration).
As the data is now filtered, the sum of the Measure column is just:
=SUM(D4:D15)
though you might need to adjust the 15 if your formuale are copied further. I've put this formula in D2.
So now you can just play about with the drop-downs in row 1, and see the display adjust accordingly.
Hope this helps.
Pete
Thanks Pete!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks