User Profile
OliverScheurich
Gold Contributor
Joined 5 years ago
User Widgets
Recent Discussions
Re: Excel formula to determine missing numbers
I've already tried several times to reply but for an unknown reason my replies in the community weren't posted. In the screenshots i don't see the error message that's returned. However i see that there's a table named "Tabelle1" with headline "CASE NUMBER" and with a cell containing "----" (exactly 4 times "-") in sheet DATA ENTRY_ALL. Can you copy the M code from my attached file and then load your table into the Power Query Editor and paste the code into the Advanced Editor of your file. After Start -> Close and load the result should look like in my sheet.Re: Excel formula to determine missing numbers
An alternative could be Power Query which works in Excel 2013 and in newer versions. In the attached file you can add data to the blue dynamic table. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table. The data layout in the screenshot and in the attached file is for illustration. You can place the green result table in another worksheet as well.Re: Data Validation Conditional Formating
This is the rule for conditional formatting in the attached sample file. =(B3=$A$17)*ISNA(MATCH(B3,$H$3:$H$17,0)) This is the applies to range for conditional formatting. =$B$3:$B$14;$D$3:$D$14;$F$3:$F$14 In cell A17 i've added Data Validation without a dropdown. In the screenshot all cells containing "July" in ranges $B$3:$B$14;$D$3:$D$14;$F$3:$F$14 are highlighted in red after "July" was removed from range H3:H17.Re: SUM WITH INDEX MATCH ERROR
=SUM(MMULT(TRANSPOSE((A5:A9=C15)*(B5:B9=C16)),TRANSPOSE(MMULT((C1:N1=C13)*(C2:N2=C14),TRANSPOSE(C5:N9))))) This formula works in my sample file and in modern and legacy Excel. The formula must be entered as an arrayformula with ctrl+shift+enter if someone doesn't work with Office 365 or Excel for the web or Excel 2024.Re: How to summarize by date range and grouping customer items
=PIVOTBY(A2:B31,LET(_daterange,HSTACK( BYROW(C2:C31,LAMBDA(x,DATE(YEAR(x),MONTH(x)-1,DAY(I1)))), BYROW(C2:C31,LAMBDA(r,LET(y,EOMONTH(r,0),DATE(YEAR(y),MONTH(y),DAY(J1)))))), DROP(REDUCE("",C2:C31,LAMBDA(u,v, VSTACK(u, XLOOKUP(1, (v>=INDEX(_daterange,,1))*(v<=INDEX(_daterange,,2)), _daterange, HSTACK( DATE(YEAR(v),MONTH(v),DAY(I1)), LET(y,EOMONTH(v,1),DATE(YEAR(y),MONTH(y),DAY(J1)))))))),1)), D2:D31,SUM, ,0) This formula returns the intended result in my Excel online sample sheet.Re: Countif/Countifs
=SUM(COUNTIF(INDEX(C:C,@CHOOSECOLS(REGEXEXTRACT(TEXTSPLIT(C3,":"),"[0-9]+"),1)): INDEX(C:C,@CHOOSECOLS(REGEXEXTRACT(TEXTSPLIT(C3,":"),"[0-9]+"),2)),{6.12.23.60})) The above formula returns the expected result in Excel 365, Excel for the web and Excel 2024. We have to make sure that there isn't a circular reference. The formula is in cell C5 and dragged to the right in row 5. That's why we can only count the occurrences of {6.12.23.60} starting from row 6 in order to avoid a circular reference. This means that all ranges between C6:C1048576 are allowed. In your formula i see one occurrance of COUNTIF*COUNTIF but i assume you always want COUNTIF+COUNTIF.Re: Excel - Matching outgoing to incoming inventory based a specific conditions
Hello Christine, thank you for your detailed explanation. I understand the task and the formula in cell K7 returns the results. The formula must be filled down. In the screenshot i've hidden some rows and highlighted 3 unique combinations of "Supplier PO" and "SKU" for illustration. I've attached the sample file with 2 sheets with hidden and unhidden rows of the sample database. Regards, Oliver =IF( OR( AND(SUMIFS($V$7:$V$1000,$D$7:$D$1000,F7,$G$7:$G$1000,G7,$J$7:$J$1000,J7) +SUMIFS($AB$7:$AB$1000,$F$7:$F$1000,F7,$G$7:$G$1000,G7,$J$7:$J$1000,J7)=0, SUMIFS($AB$7:$AB$1000,$F$7:$F$1000,F7,$G$7:$G$1000,G7,$J$7:$J$1000,J7)<0), AND(SUMIFS($AB$7:$AB$1000,$F$7:$F$1000,D7,$G$7:$G$1000,G7,$J$7:$J$1000,J7)+V7=0, SUMIFS($AB$7:$AB$1000,$F$7:$F$1000,D7,$G$7:$G$1000,G7,$J$7:$J$1000,J7)<0)), "closed", IF( OR(SUMIFS($V$7:$V$1000,$D$7:$D$1000,F7,$G$7:$G$1000,G7,$J$7:$J$1000,J7) +SUMIFS($AB$7:$AB$1000,$F$7:$F$1000,F7,$G$7:$G$1000,G7,$J$7:$J$1000,J7)<>0, AND(SUMIFS($AB$7:$AB$1000,$F$7:$F$1000,D7,$G$7:$G$1000,G7,$J$7:$J$1000,J7)+V7<>0, SUMIFS($AB$7:$AB$1000,$F$7:$F$1000,D7,$G$7:$G$1000,G7,$J$7:$J$1000,J7)<0)), "in progress", "") )Re: Excel - Matching outgoing to incoming inventory based a specific conditions
Hello Christine, thank you for your detailed explanation. Unfortunately i didn't understand earlier what should be done. This formula spills the results. However the LAMBDA REDUCE VSTACK has some limitations therefore i've added basically the same formula in cell M7 of the attached sample file. The formula in M7 must be filled down and it doesn't have the limitations of the LAMBDA REDUCE VSTACK formula. =LET(_data,CHOOSECOLS(B7:AB66,1,3,6,9,21,27),DROP(REDUCE("",SEQUENCE(ROWS(_data)), LAMBDA(w,x,VSTACK(w,IF(INDEX(_data,x,1)="in","",LET(_rng,TAKE(_data,x), _filter,FILTER(_rng,(CHOOSECOLS(_rng,3)=INDEX(_rng,x,3))*(CHOOSECOLS(_rng,4)=INDEX(_rng,x,4))), _sequence,SEQUENCE(-SUM(TAKE(_filter,,-1))), _totalin,ROUNDUP(_sequence/INDEX(_filter,1,5),0), UNIQUE(TAKE(_totalin,INDEX(_rng,x,6)))))))),1)) Kind regards, OliverRe: Excel - Matching outgoing to incoming inventory based a specific conditions
Hello Christine, you are welcome. In your latest screenshot the scenario seems to have changed completely and unfortunately i'm not sure what you want to do. Perhaps the screenshot details represent your expected result. Do the Skid 1, Skid 2 .... show what you mean by skid and does range E4:E24 show the intended output? Perhaps we only need to sequentially count how often "out" occurs within each skid. =LET(_num,SCAN(0,C4:C24,LAMBDA(a,b,IF(b="out",a+1,0))),IF(_num=0,"",_num)) Please let me know what Excel version you work with. This suggestion works with current Excel online and Microsoft 365. Kind regards, OliverRe: Excel - Matching outgoing to incoming inventory based a specific conditions
Hello Christine, you are welcome. Please try this formula that is in cell E7 and filled down in my sample sheet. I have to enter the formula as an arrayformula with ctrl+shift+enter because i work with the download version of Excel 2013. =IF(D7<>"","",INDEX($D$7:D7,LARGE(IF($D$7:D7<>"",ROW($D$1:D1)),1)))Re: Excel - Matching outgoing to incoming inventory based a specific conditions
=IF(SUMIFS($H$2:$H$1000,$D$2:$D$1000,D2)+SUMIFS($I$2:$I$1000,$D$2:$D$1000,D2)=0,"closed", IF(SUMIFS($I$2:$I$1000,$D$2:$D$1000,D2)=0,"open", IF(SUMIFS($H$2:$H$1000,$D$2:$D$1000,D2)+SUMIFS($I$2:$I$1000,$D$2:$D$1000,D2)>0,"in progress",""))) This is the formula in cell G2. =IF(A2<>"","",INDEX($A$2:A2,LARGE(IF($A$2:A2<>"",ROW($A$1:A1)),1))) This is the formula in cell B2. The formula must be entered as an arrayformula with ctrl+shift+enter if you work with legacy Excel such as Excel 2013. Both formulas must be filled down.Re: Help with formula
=INDEX('My ?ing'!E:E,MATCH(1,(MONTH('My ?ing'!B:B)=2)*(ISNUMBER(SEARCH("Discover",'My ?ing'!C:C))),0)) This formula works in my sample file. The formula must be entered as an arrayformula with ctrl+shift+enter if someone works with legacy Excel such as Excel 2013. In the attached file i've added the formula in cell C11 in order to check if it works. =IFNA(IF(C$2="Paid",INDEX('My ?ing'!$E:$E,MATCH(1,(MONTH('My ?ing'!$B:$B)=MONTH(C$1))*(ISNUMBER(SEARCH($B3,'My ?ing'!$C:$C))),0)), INDEX('My ?ing'!$B:$B,MATCH(1,(MONTH('My ?ing'!$B:$B)=MONTH(C$1))*(ISNUMBER(SEARCH($B3,'My ?ing'!$C:$C))),0))),"") In the attached file i've added a formula that pulls dates and amount from the "My ?ing" sheet. The formula is in cell C3 and dragged across range C3:F6. In range C1:F1 are dates 01.01.2025 and 01.02.2025 that are formatted as "januar" and "februar".Re: [Excel Formula?] How to calculate a product's sales rank based on 3 criteria
=LET(arr,LAMBDA(x,CHOOSE(x,SEQUENCE(ROWS(A2:A9)),SEQUENCE(,COLUMNS(D1:F1)))), res,MAP(arr({1}),arr({2}),LAMBDA(a,b,COUNTIFS(A2:A9,INDEX(A2:A9,a,),A2:A9,RIGHT(CHOOSECOLS(D1:F1,b),4),C2:C9,">="&INDEX(C2:C9,a,)))), IF(res=0,"",res)) In Excel for the web and Excel 365 you can use this formula that spills the result. It uses HansVogelaar 's COUNTIFS and spills the result. Thanks as well to m_tarler for his PIVOTBY with very instructive use of LAMBDA and XMATCH.Re: Totalling money in categories and sub-categories across multiple columns
In the attached file the categories are text values (left aligned) and the allocated $ are numbers (right aligned). =SUM(IF($B$2:$F$9=B12,$C$2:$G$9)) This formula works in my sample file. The formula must be entered as an arrayformula with ctrl+shift+enter if someone works with legacy Excel such as Excel 2013.
