Array formula with match index or any other required

[akshay6s]

HI,

i have a file in which sheet "order" in cell E3 to S i want aan array formula which can show the exact result.
i need to have total of E column (column order in sheet "unique entries". for all the po no. e2:s2 (sheet "order")

and that po number column is Q in sheet "unique entries".

i want to match column A,b,c,d and e2:s2 in sheet "order" and all of these columns will get match from sheet "unique entries" respectively q,b,c,d,a from sheet "unique entries.

for example:- if we apply see po no. 4090 (f2) and after matching a3:d3 the result is 56.

please solve this query.

[JohnTopley]

Are SKUs not unique to columns B:D and therefore match on SKU and P.O

In E3

=IFERROR(INDEX(Sheet1!$E2:$E500,MATCH(order!E$2&order!$A3,Sheet1!$A2:$A500&Sheet1!$P2:$P500,0)),"")

Enter with Ctrl+Shift+Enter

[Pete_UK]

You can use this formula in E3 of the Order sheet:

=SUMIFS(Sheet1!$O:$O,Sheet1!$Q:$Q,E$2,Sheet1!$P:$P,$A3,Sheet1!$B:$B,$B3,Sheet1!$C:$C,$C3,Sheet1!$D:$D,$D3)

(this is NOT an array formula), then copy across and down as required.

Hope this helps.

Pete

[JohnTopley]

Shorter

=SUMIFS(Sheet1!$E:$E,Sheet1!$A:$A,E$2,Sheet1!$P:$P,$A3)

Based on SKU

[Pete_UK]

John,

to get the same results as my other formula, I had to use this:

=SUMIFS(Sheet1!$O:$O,Sheet1!$Q:$Q,E$2,Sheet1!$P:$P,$A3)

Maybe there are some numbers as text in the raw data.

Pete

[JohnTopley]

OP wanted data from Column E but I missed the requirement for column Q!

=SUMIFS(Sheet1!$E:$E,Sheet1!$Q:$Q,E$2,Sheet1!$P:$P,$A3)

[akshay6s]

thanks, it is working but i wanted to have this as array formula so that there will be no need of dragging the formula. as the data will increase the result will come by itself. please make it array formula or if not possible with sum ifs then with any other. i am insisting on array because my original file is getting stucked too much as it contains too much data. here i have posted the excel file for example only. however i got the solution really thanks for that but please give me array formula.

[akshay6s]

and really thanks for you also. i have tried the last one formula and that is working fine but i really need an array formula. because i have 3 sheets and a very lengthy data. so really need that. thanks again for you formulas.

[JohnTopley]

I don't understand what you mean by an "array formula" i this situation. Are you meaning a "spill" formula as per Excel 365 for example. as I assume "dragging" refers to copying the formula down the column If so, please update your profile to reflect your Excel version.

And please post a file which represents the true situation as you state you have 3 sheets with data.

[akshay6s]

please visit here on the link which i am posting below. this is my original google sheets file link. and please see E3 in sheet "lb order". i got that solved. but please visit to see what i meant with array formula so that in future there should be no problem. and when i am uploading excel file with my original data then file size is getting bigger than 1 mb which is not supported by the forum. so giving the link.

https://docs.google.com/spreadsheets...#gid=877589508

[AliGW]

So is this an Excel query or a Google Sheets query?

[akshay6s]

does array formula not work in excel. and firstly i had posted this as an excel query. because more then 1 mb file size is not allow to upload so i provided the link. now there are both options available excel and google sheet.

[AliGW]

OK - the ARRAYFORMULA function is a Google Sheets function, not an Excel one. So, is this for Google Sheets or Excel?

[akshay6s]

i got the formula for my sheet. but still i need a little change in it. in cell E3 formula should be shown up the result only for po no. appears. i mean to say that we have given refrence in the formula for E2:S2. but po no. are available onlyb till E2:R2 in sheet shipped. so it is showing zero in s256:s and same for sheet "balance" I235:S

so please adjust the formula in this way that it stay blank instead of zero if po no. are not present in any of the cell E2:S2

and i want this change to have in my google sheets or give me formula here.

[JohnTopley]

In Excel format cells as

0;-0;;@

This will suppress zeros.

[akshay6s]

however you have solved my problem according to excel. so thanks a lot.