problem with xlook up if lookup array colume has duplicate values

[mahmoud ibrahim 1989]

hello every one

as you see the formulas cell is the red one and there are two sheets the left one is total
so i try to get the amount from total (sheet) to the right sheet
but there are duplicate values in lookup array (F) column what is solution to consider the duplicate values
to get the amount( return array) of each value so the total of amount in two sheets is equal (don't miss amount of any duplicated value )

thanks

 

[etaf]

sumif()
rather than a lookup
then you will add any duplicate values as well to the total

 

[mahmoud ibrahim 1989]

sumif()
rather than a lookup
then you will add any duplicate values as well to the total

thank you brother but how i use
you must know that the sheet in right dont have any amount i will get the amount consider the number of WO with xlookup
but the numbers of WO in the left are have duplecation

 

[etaf]

=sumif( $F:$F ,BV9, $O:$O)
add the table name and worksheet name
difficult to see in an image and NOT UK language , so not sure of the letters used
BUT the sumif will not work if the workbook is closed - both have to be open

for a closed workbook, may need a SUMPRODUCT() - is this case the workbook is closed or open

all in 1 sheet - so your filename and sheet need to be added and use a range - not column reference

Book3
	A	B	C	D	E	F	G
1	lookup	return		criteria	SUMIF		SUMPRODUCT
2				a	18		18
3
4	a	12
5
6
7
8	a	6
9
Sheet3

Cell Formulas
Range	Formula
E2	E2	=SUMIF(A2:A15,D2,B2:B15)
G2	G2	=SUMPRODUCT((A2:A16=D2)*(B2:B16))

Note: Images are difficult to see , and also requires that I input all the data myself, which means I may make an error, which is very time consuming, and from my point of view less likely to get a response, if a complicated spreadsheet. Plus we cannot see any of the formulas used.

Therefore -

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

---

 

[mahmoud ibrahim 1989]

hello etaf
thanks for you respond
here is the book1

Loading Google Sheets

and book 2

https://docs.google.com/spreadsheets/d/1zApG6SDCcarqB4jQ390hn3YBNtGChprb/edit?usp=drive_link&ouid=107650598928329685155&rtpof=true&sd=true

i want to get amount and its date from book 2 to book 1 depend on W/O Coloumn in two sheets​

and you shoud know that sometimes W/O in book 2 is duplicated two times at all so it has two amount

how i fill all amount and date from book2 to book 1

appreciated

​

 

[etaf]

a small sample with expected results - would have been better
however
=SUMIF([Book2.xlsx]Sheet1!$C$4:$C$10000,A8,[Book2.xlsx]Sheet1!$F$4:$F$10000)

i have saved the files and created a formula - which will simply SUM column F in book2 - based on the criteria in A and book2 column C

and sumproduct which will work if book2 is closed

Book1.xlsx
	A	B	C	D	E	F	G	H
5			16,949,045.63
6								sumproduct
7	W/O	Type	Paid Amount	Paid Date	Paid Amount	Paid Date2
8	80226293	7			-			0
9	122030771	1			-			0
10	122057105	1			-			0
11	132185004	1			-			0
12	132231537	7			-			0
13	132269890	73			-			0
14	142015390	7			-			0
15	142028506	7			-			0
16	142060218	2			-			0
17	142064990	7			-			0
18	142065581	7			-			0
19	222002680	506	230.00	1/26/23	230.00			230
20	142144332	7			-			0
21	142157435	73			-			0
22	142174760	7			-			0
23	142208137	23			-			0
24	222002683	506	230.00	1/26/23	230.00			230
25	142225223	1			-			0
26	142248798	23			-			0
27	142248804	23			-			0
Sheet1

Cell Formulas
Range	Formula
C5	C5	=SUBTOTAL(9,C8:C999998)
E8:E27	E8	=SUMIF([Book2.xlsx]Sheet1!$C$4:$C$10000,A8,[Book2.xlsx]Sheet1!$F$4:$F$10000)
H8:H27	H8	=SUMPRODUCT(([Book2.xlsx]Sheet1!$C$4:$C$10000=A8)*([Book2.xlsx]Sheet1!$F$4:$F$10000))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A:A	Cell Value	duplicates	text	NO
A7340:A1048576,A1:A7330	Cell Value	duplicates	text	NO
A5363:A5647,A5649:A5812,A5822:A7255,A1:A5353,A7257:A7330,A7340:A1048576	Cell Value	duplicates	text	NO
A9687:A1048576,A1:A9	Cell Value	duplicates	text	NO
A9687:A1048576,A1:A9	Cell Value	duplicates	text	NO
A9687:A1048576,A1:A9	Cell Value	duplicates	text	NO
A1:A5	Cell Value	duplicates	text	NO
A1:A5	Cell Value	duplicates	text	NO
A1:A5	Cell Value	duplicates	text	NO
A1:A5	Cell Value	duplicates	text	NO
A1:A5	Cell Value	duplicates	text	NO
A1:A5	Cell Value	duplicates	text	NO
A1:A5	Cell Value	duplicates	text	NO
A1:A5	Cell Value	duplicates	text	NO
A1:A5	Dates Occurring	yesterday	text	NO
A9687:A1048576,A6:A9	Cell Value	duplicates	text	NO
A9687:A1048576,A7	Cell Value	duplicates	text	NO
A9687:A1048576,A7	Dates Occurring	yesterday	text	NO
A9687:A1048576,A7	Cell Value	duplicates	text	NO
A9687:A1048576,A7	Cell Value	duplicates	text	NO
A9687:A1048576,A7	Cell Value	duplicates	text	NO
A9687:A1048576,A7:A9	Cell Value	duplicates	text	NO
A10:A11	Cell Value	duplicates	text	NO
A10:A11	Cell Value	duplicates	text	NO
A10:A11	Cell Value	duplicates	text	NO
A6067:A6119,A6121:A7255,A5183:A5353,A5363:A5647,A5649:A5812,A5822:A6064,A12:A5181,A7257:A7330,A7340:A9686	Cell Value	duplicates	text	NO
A6067:A6119,A6121:A7255,A5183:A5353,A5363:A5647,A5649:A5812,A5822:A6064,A12:A5181,A7257:A7330,A7340:A9686	Cell Value	duplicates	text	NO
A6067:A6119,A6121:A7255,A5183:A5353,A5363:A5647,A5649:A5812,A5822:A6064,A12:A5181,A7257:A7330,A7340:A9686	Cell Value	duplicates	text	NO
A6067:A6119,A6121:A7255,A5183:A5353,A5363:A5647,A5649:A5812,A5822:A6064,A1:A5181,A7257:A7330,A7340:A1048576	Cell Value	duplicates	text	NO

 

[mahmoud ibrahim 1989]

sorry for this but the XL2BBa didnt work with me
&
thats right sum if will do it
but as a told you some times W/O cells Column(in book 2) is duplicated so it will sum atotal of amount of all cell_(W/O)

but i need it two times in amount and date
such as this cell in book 2

212049586

it shows in row 4 and row 214
so sumif will get it as one number as a total
but i want it two numbers and its date

i know this seems to be impossible but if not works
i should do this manulay
and as u see its a lot a lot of work

i hope u under stand me
and sorry for bad language


appreciated

 

[etaf]

thats why a small sample would be much better

if this does not answer the question - then provide 2 very simple spreadsheets with 10 - 20 rows and expected results as mentioned above -
I filters book2 on

212049586

and got 2 entries
S/N Date W/O Type Invoice Number Amount (S.R) Remarks
4 2023/01/05 212049586 802 2301000996 16,131.80
5 2023/03/02 212049586 801 2301001063 471.00

and not row 4 / 214 -

you mention dates
i dont see any dates in book1

If you just want to sum unique entries

i have just changed the 1st couple of lines to show duplicates
and used

=SUM(UNIQUE(FILTER([Book2.xlsx]Sheet1!$F$4:$F$10000,[Book2.xlsx]Sheet1!$C$4:$C$10000=A8)))

entry in row 4 and 5 on book2
now contains W/O = 132251036
both with a value of 10
so a SUM will add that up to 20
But by using the formula above - it returns 10 and ignores duplicated W/O numbers

Book1.xlsx
	A	B	C	D	E	F
7	W/O	Type	Paid Amount	Paid Date	Paid Amount	Paid Date2
8	132251036	7			10.00
Sheet1

Cell Formulas
Range	Formula
E8	E8	=SUM(UNIQUE(FILTER([Book2.xlsx]Sheet1!$F$4:$F$10000,[Book2.xlsx]Sheet1!$C$4:$C$10000=A8)))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A8	Cell Value	duplicates	text	NO
A1:A7,A9:A1048576	Cell Value	duplicates	text	NO
A7340:A1048576,A1:A7,A9:A7330	Cell Value	duplicates	text	NO
A5363:A5647,A5649:A5812,A5822:A7255,A1:A7,A7257:A7330,A7340:A1048576,A9:A5353	Cell Value	duplicates	text	NO
A9687:A1048576,A1:A7,A9	Cell Value	duplicates	text	NO
A9687:A1048576,A1:A7,A9	Cell Value	duplicates	text	NO
A9687:A1048576,A1:A7,A9	Cell Value	duplicates	text	NO
A9687:A1048576,A6:A7,A9	Cell Value	duplicates	text	NO
A9687:A1048576,A7	Cell Value	duplicates	text	NO
A9687:A1048576,A7	Dates Occurring	yesterday	text	NO
A9687:A1048576,A7	Cell Value	duplicates	text	NO
A9687:A1048576,A7	Cell Value	duplicates	text	NO
A9687:A1048576,A7	Cell Value	duplicates	text	NO
A9687:A1048576,A7,A9	Cell Value	duplicates	text	NO
A6067:A6119,A6121:A7255,A5183:A5353,A5363:A5647,A5649:A5812,A5822:A6064,A1:A7,A7257:A7330,A7340:A1048576,A9:A5181	Cell Value	duplicates	text	NO

Book2.xlsx
	A	B	C	D	E	F	G
3	S/N	Date	W/O	Type	Invoice Number	Amount (S.R)	Remarks
4	1	2023/01/02	132251036	8	2301000475	10.00
5	1	2023/01/05	132251036	85	2301000494	10.00
6	1	2023/01/05	152084154	85	2301000494	30,517.50
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C3:C2342	Cell Value	duplicates	text	NO