Excel - SUM Function Problem - Expert Solution

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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.