Forum Discussion
Help with formula
Hello,
I literally can not come up with the solution, and I have tried sum product or sum ifs,
match 2 criteria`s to find the data belonging to it. Which formula should I be using? index&match ?
so basically what I am trying is get the data show up in colom x that was booked with invoice2 and prod1 etc
- You can do this with an array formula. First, you never did use the -200 number (row invoice4, column prod4, in your summary table showing in Column V & Column W). Not really sure if that was intentional or accidental. All you need to do is add a row in Columns V & W that contains invoice4 prod4 and it will work.
The array formula is:
=SUM(IF(($D$3:$D$14=$V5)*($F$2:$N$2=W5),$F$3:$N$14))
IMPORTANT: you must hit CTRL-SHIFT-ENTER at the end of entering that equation (not a regular enter). That will put { } brackets around your equation. You do not type those brackets manually.
The logic behind this is we want to search through $D$3:$D$14 (the column of invoice names) and whatever matches the invoice in your Column V, and also search through row $F$2:$N$2 (the row of prod1, prod 2, etc.), and whatever matches the prod in your column W, add the corresponding cell within the range $F$3:$N$14.
After you do the CTRL-SHIFT-ENTER, then copy that cell, and paste it in your Column X, but begin at the 2nd row (invoice2 prod2) down to (invoice8 prod5).- thank you, I didn't think of that!
Steven35-31 All the following formulas should work for you.
=SUMPRODUCT(($F$3:$N$14)*($D$3:$D$14=V5)*($F$2:$N$2=W5)) =FILTER(FILTER($F$3:$N$14,$D$3:$D$14=V5),$F$2:$N$2=W5) =SUM(XLOOKUP(W5,$F$2:$N$2,XLOOKUP(V5,$D$3:$D$14,$F$3:$N$14)))Steven35-31 Another option is to unpivot the data using a set criteria. For example:
=LET( UNPVT, LAMBDA(n, TOCOL(IFS(F3:T1000<0, CHOOSE(n, D3:D1000, F2:T2, F3:T1000)), 2)), HSTACK(UNPVT(1), UNPVT(2), UNPVT(3)) )Sample Results
Based on your sample data, I assumed all "(booking)" amounts are positive and all invoiced amounts are negative, so I set the criteria to return only records with an amount less than zero. If that's not the case, another variation could be:
=LET( UNPVT, LAMBDA(n, TOCOL(IFS(F3:T1000<>"", CHOOSE(n, D3:D1000, F2:T2, F3:T1000)), 2)), FILTER(HSTACK(UNPVT(1), UNPVT(2), UNPVT(3)), UNPVT(1)<>"(booking)") )See attached...
