Question description:
This user has given permission to use the problem statement for this
blog.
hello I am trying to sum the values in a multi column and multi roq array if the column is equal to a certain value and the row is equal to a certain value
Solved by I. Q. in 40 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
14/04/2018 - 08:15
Hi there
User
14/04/2018 - 08:16
hello
Excelchat Expert
14/04/2018 - 08:16
How are you and how may i assist you with today?
User
14/04/2018 - 08:17
I am pretty good. I have aquestion about summing the values in a multi column and multi row array if the header of a column has a certain value and the header of a row has acertain value
Excelchat Expert
14/04/2018 - 08:17
Okay, i hope excel has that property to do that
Excelchat Expert
14/04/2018 - 08:17
i will try my best to help you out with this
Excelchat Expert
14/04/2018 - 08:17
Can you share me a sample worksheet
Excelchat Expert
14/04/2018 - 08:18
So that we both can have a better evaluation to the issue
User
14/04/2018 - 08:19
i have enterd what my array looks like but smaller
Excelchat Expert
14/04/2018 - 08:20
okay, that may workout for us
User
14/04/2018 - 08:20
ok i am trying to find the sum of asphalt if on 4/13/2018
Excelchat Expert
14/04/2018 - 08:20
okay, let me see that, what i can do for you
Excelchat Expert
14/04/2018 - 08:21
Just give me a while
User
14/04/2018 - 08:21
ok
Excelchat Expert
14/04/2018 - 08:21
:)
User
14/04/2018 - 08:22
i basically need to know what formula i can use for this solution
Excelchat Expert
14/04/2018 - 08:23
Answer should be 100, right?
User
14/04/2018 - 08:23
answer should be 400
Excelchat Expert
14/04/2018 - 08:23
yep, sorry got it
User
14/04/2018 - 08:31
any luck
Excelchat Expert
14/04/2018 - 08:31
just a bit more, hang on
Excelchat Expert
14/04/2018 - 08:37
It will be done using Sumproduct formula of excel :)
User
14/04/2018 - 08:37
could you please provide the formula in the shared document?
Excelchat Expert
14/04/2018 - 08:38
trying to do it, will share the sheet shortly
Excelchat Expert
14/04/2018 - 08:45
just a bit more
Excelchat Expert
14/04/2018 - 08:49
got it
User
14/04/2018 - 08:49
is the formula i punched in correct
Excelchat Expert
14/04/2018 - 08:49
it was a quite interesting though
User
14/04/2018 - 08:49
=SUMPRODUCT((A2:A5=ʺasphaltʺ)*(B1:E1=D1)*(B2:E5))
Excelchat Expert
14/04/2018 - 08:49
yep,
Excelchat Expert
14/04/2018 - 08:49
thats nice
User
14/04/2018 - 08:49
thanks for you help
Excelchat Expert
14/04/2018 - 08:50
also there's another way
User
14/04/2018 - 08:50
how
Excelchat Expert
14/04/2018 - 08:50
and a simple way to do this, and its quite dynamic
Excelchat Expert
14/04/2018 - 08:51
check in sheet 2
Excelchat Expert
14/04/2018 - 08:52
just change up anything and you will get the value
Excelchat Expert
14/04/2018 - 08:53
any thing else you need help for this query?
Excelchat Expert
14/04/2018 - 08:53
Please do a valuable rating for me
Excelchat Expert
14/04/2018 - 08:53
that will help me a lot
Excelchat Expert
14/04/2018 - 08:53
Are you there?
Excelchat Expert
14/04/2018 - 08:54
Thanks for being in Got it Pro!
Excelchat Expert
14/04/2018 - 08:54
Have a nice day!
Excelchat Expert
14/04/2018 - 08:55
And nice to e-meet you!
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.