Question description:
This user has given permission to use the problem statement for this
blog.
I have 3 sheets with different information that i need to link to get a valid sum
Solved by Z. J. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
User
07/02/2018 - 06:44
hi
Excelchat Expert
07/02/2018 - 06:45
Hello :)
Excelchat Expert
07/02/2018 - 06:45
you please send upload file?
User
07/02/2018 - 06:46
the file is too big to send i will paste some infomation here
Excelchat Expert
07/02/2018 - 06:47
yes
Excelchat Expert
07/02/2018 - 06:47
please
User
07/02/2018 - 06:48
sorry my excel is slow
Excelchat Expert
07/02/2018 - 06:49
please then explain in details what do you want
Excelchat Expert
07/02/2018 - 06:49
no worries
Excelchat Expert
07/02/2018 - 06:49
will be extended 2 times
User
07/02/2018 - 06:49
ok
User
07/02/2018 - 06:51
what i need is to calculate the cost of APDI-025
User
07/02/2018 - 06:51
but has to find it through the number located in cell d8
User
07/02/2018 - 06:52
so the formula has to find cell d8
User
07/02/2018 - 06:52
in sheet 2 first
User
07/02/2018 - 06:52
which is cell c2
User
07/02/2018 - 06:53
then find cell d2 in sheet 2
User
07/02/2018 - 06:53
sorry the number in d2 find it in sheet 3
Excelchat Expert
07/02/2018 - 06:53
ok
User
07/02/2018 - 06:54
and then return 1020
Excelchat Expert
07/02/2018 - 06:54
and sum by which values (columns) in those sheets?
Excelchat Expert
07/02/2018 - 06:54
at is 1020?
Excelchat Expert
07/02/2018 - 06:54
cost per hr?
User
07/02/2018 - 06:54
only column q in sheet 3
Excelchat Expert
07/02/2018 - 06:55
ok, and in sheet 2?
User
07/02/2018 - 06:55
you need to reference sheet 2 to get the Conca number in cell d2
Excelchat Expert
07/02/2018 - 06:56
i understood :)
User
07/02/2018 - 06:56
but it needs to find the number in a column of data
Excelchat Expert
07/02/2018 - 06:57
1st find Conca number in sheet 2 by MaintItem
User
07/02/2018 - 06:57
yes
Excelchat Expert
07/02/2018 - 06:57
then sum sheet 3 Column Q by that Conca number
Excelchat Expert
07/02/2018 - 06:57
and the output will be $ 5 160
Excelchat Expert
07/02/2018 - 06:57
yes?
User
07/02/2018 - 06:58
use the conca number but only sum rows that have APDI-025
Excelchat Expert
07/02/2018 - 06:58
ah ok
Excelchat Expert
07/02/2018 - 06:58
in column L
Excelchat Expert
07/02/2018 - 06:58
now its clear
User
07/02/2018 - 06:58
correct
Excelchat Expert
07/02/2018 - 06:58
ok wait now pls
User
07/02/2018 - 06:59
sorry and also if there is a 1 in coloumn I
Excelchat Expert
07/02/2018 - 06:59
????
Excelchat Expert
07/02/2018 - 06:59
in column a there is a text
Excelchat Expert
07/02/2018 - 06:59
in sheet 3
Excelchat Expert
07/02/2018 - 06:59
?\
User
07/02/2018 - 07:00
on sheet 1 column I there is a 1 if i take that out there should be 0 for apdi-025
Excelchat Expert
07/02/2018 - 07:01
ok
Excelchat Expert
07/02/2018 - 07:04
ready Sir
User
07/02/2018 - 07:05
ok
Excelchat Expert
07/02/2018 - 07:05
The formula SUMs only when you put 1 in Type1 colum
Excelchat Expert
07/02/2018 - 07:05
please check it
Excelchat Expert
07/02/2018 - 07:05
and please don't forget to rate our session :)
User
07/02/2018 - 07:06
excellent
User
07/02/2018 - 07:06
one last thing
Excelchat Expert
07/02/2018 - 07:07
sure
User
07/02/2018 - 07:07
how about if i want it to show the rsult in cell b8
Excelchat Expert
07/02/2018 - 07:07
just cut and paste it from j8
User
07/02/2018 - 07:07
and with out direct referencing cell d9
User
07/02/2018 - 07:08
i want the formula to find 100104761 in column d
Excelchat Expert
07/02/2018 - 07:08
but how then?
User
07/02/2018 - 07:08
not use d9
User
07/02/2018 - 07:09
sorry d8
Excelchat Expert
07/02/2018 - 07:09
but then it will not work for other Manitems
User
07/02/2018 - 07:10
sorry that will not work
User
07/02/2018 - 07:11
i will try get more information for you
Excelchat Expert
07/02/2018 - 07:11
ready
Excelchat Expert
07/02/2018 - 07:11
you requested
Excelchat Expert
07/02/2018 - 07:11
i put 1000104761 manually
User
07/02/2018 - 07:11
hold on i will add more information for you
Excelchat Expert
07/02/2018 - 07:16
ok
User
07/02/2018 - 07:17
so it needs to find both items that have 1 in column I
User
07/02/2018 - 07:17
and represent in cell b8
Excelchat Expert
07/02/2018 - 07:18
what about A8?
Excelchat Expert
07/02/2018 - 07:18
it will stay the same?
User
07/02/2018 - 07:18
yes will stay the same
Excelchat Expert
07/02/2018 - 07:18
APDI-025
Excelchat Expert
07/02/2018 - 07:18
ok
Excelchat Expert
07/02/2018 - 07:18
the same output
User
07/02/2018 - 07:19
i need it in cell b8
Excelchat Expert
07/02/2018 - 07:20
you want to sum both?
Excelchat Expert
07/02/2018 - 07:20
1020+1020?
User
07/02/2018 - 07:20
the problem is if i had to sum 400 rows of data
Excelchat Expert
07/02/2018 - 07:20
listen,, everything is correct
User
07/02/2018 - 07:21
okay i wll get lots of data and you can see what i mean
Excelchat Expert
07/02/2018 - 07:22
lets analyse t together
Excelchat Expert
07/02/2018 - 07:22
you added 1000104760 to sheet 1
Excelchat Expert
07/02/2018 - 07:22
so its conca Number is 200000918
Excelchat Expert
07/02/2018 - 07:22
and my formula
Excelchat Expert
07/02/2018 - 07:23
sum ups all Cost Per RWC that have APDI-025 in column L
Excelchat Expert
07/02/2018 - 07:24
and 200000918 (conca Number ) in column B in sheet 3
Excelchat Expert
07/02/2018 - 07:24
so the output is again $ 1 020
Excelchat Expert
07/02/2018 - 07:24
everytime when your data will be added
Excelchat Expert
07/02/2018 - 07:25
my formula will do SUM by same method if you put 1 in Type 1 formula
Excelchat Expert
07/02/2018 - 07:25
so what is the problem?
User
07/02/2018 - 07:27
Okay i need the total for all items that have 1 in them in column i and sumed in cell b8
User
07/02/2018 - 07:39
?
User
07/02/2018 - 07:41
hello
User
07/02/2018 - 07:41
are u still there
User
07/02/2018 - 07:43
?
User
07/02/2018 - 07:43
?
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
