User Profile
OliverScheurich
Gold Contributor
Joined 5 years ago
User Widgets
Recent Discussions
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.Re: Formula help - not sure where to start
=SMALL(IF((A2:A12=F1)*(B2:B12<=G1)*(LARGE(IF(B2:B12<=G1,B2:B12),1)<=B2:B12),C2:C12),1) An alternative for legacy Excel could be this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel for the web or Excel 2021 or Excel 2024.Re: PIVOTBY function: SUM turns into "_xleta.SUM"
You can use LAMBDA(x,SUM(x)) in english Excel instead of only SUM I experience the same problem in Excel online if i use only SUMME or MITTELWERT or ZEILEN in german Excel. The "_xleta." doesn't appear anymore if i use LAMBDA(x;SUMME(x)) or LAMBDA(x;MITTELWERT(x)) or LAMBDA(x;ZEILEN(x))..... This means that if i apply SUMME or MITTELWERT or ZEILEN in german Excel "_xleta.SUM" or "_xleta.AVERAGE" or "_xleta.ROWS" (in english) is returned from time to time. Unfortunately i don't know why this happens with SUM (SUMME) or AVERAGE (MITTELWERT) or ROWS (ZEILEN)..... In spanish Excel you can use LAMBDA(x;SUMA(x)), in french Excel LAMBDA(x;SOMME(x)), in hungarian Excel LAMBDA(x;SZUM(x)) ....Re: Help creating weighted average ranking
=SUM(MMULT(TRANSPOSE($A$2:$A$11),N($B$2:$D$11=G2)))/COUNTIFS($B$2:$D$11,G2) This works in my sample sheet. 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 2021 or Excel 2024.
