Simple Sumif problem to deal with (Only have twist)

[kuldeepjainesl]

Hi Experts,


By the help of you guys I have populated a matrix which is large then what I though. Need to furnish it with sumif but it do not seems to work to sum an array.


Details at http://chandoo.org/forums/topic/design-a-matrix-from-two-way-look-up-and-ranges


Now what I need is explained below:

[pre]

"A"-----------Another worksheet ("A")  "B" "C" "D" "E" "F"
2000----------2000
2001----------2000
2002----------2000
2003----------2003
2004----------2003
2005----------2003
2006----------2008
2007----------2008
2008----------2008
""  ----------2007
""  ----------2007
""  ----------2007

[/pre]
Now by using =SUMIF('Data Processing'!$A$3:$A$237,D$2,'Data Processing'!$E$3:$E$237) i can sum the value under "E" coloum but when I changed it to $B$3:$E$237, it do not work.


if I use {=SUM(IF('Data Processing'!$A$3:$A$237=D$2,'Data Processing'!$E$3:$O$237,0))}


I gives the total but ignore the condition SUMIF('Data Processing'!$A$3:$A$237,D$2.


Any help is appreciated...


Regards,

Kuldeep

 

[kuldeepjainesl]

Hi Guys,


I could find out a workaround to this problem by using a helper coloum and used = SUMIF('Data Processing'!$A$3:$A$237,"="&$A3,'Data Processing'!E$3:E$237)


While previously I was trying to sum array, but now I could sum the array in one coloum and used it with simple Sumif.


In case still I get the formula which can sum the array. it will be more easy way to do it.


Regards,

Kuldeep

 

[Luke M]

Assuming your data is something like this:

[pre]

a	b	a
60	18	7
51	71	84

[/pre]
If you wanted to do a conditional sum, say add up all the numbers in columns with "A", the formula would be:

=SUMPRODUCT((A1:C1="a")*(A2:C3))

Hope that gives you some ideas.

 

[kuldeepjainesl]

Hi Luke M


In this case A1:C1 range is not there.


I have unique values in A coloum and a number of values in C coloum which are expected to be from coloum A. Now i need to sum all rows from D to H where the condition matches.


In this case first 2 row sum will be placed for A1 "2000" and sum of 3rd row to to 7th row will placed in for A5 "2003"


"A"----------"C" "D"E"F"G"H"

2000---------2000

2001---------2000

2002---------2003

2003---------2003

2004---------2003

-------------2003

-------------2003


Hope i could explain....


Luke M, I may be bothering you with these question but these all the condition i could not solve in my past and hope to get these clear now.


Nowadays i am working on worksheet & i might be asking few question to fourm but would not be able to post the file on public place due to its confidentiality. May i have your id in case i need to send the file to you to explain it. My id is kuldeep.jain@live.com


Regards,

Kuldeep

 

[Luke M]

Sounds like you just need to flip the formula I gave you 90 degrees.

First formula:

=SUMPRODUCT(($C$1:$C$1000=$A1)*($D$1:$H$1000))


Copy down as needed.

 

[kuldeepjainesl]

Thanks Luke M.


It seems that most of my problem will get resolve if i fully understand the Sum product. Really powerful function.


Do you have any sheet on Sum product like we have "Cheater_John_Excel_VLOOKUP_Chandoo.pdf" at

http://chandoo.org/wp/2010/11/19/download-vlookup-cheatsheet/


This really helped me a lot to understand.


Thanks

Kuldeep

 

[Luke M]

Check out this post for a short explanation:

http://chandoo.org/wp/2009/11/10/excel-sumproduct-formula/


Or here, for even more in-depth examples:

http://www.excelhero.com/blog/2010/01/the-venerable-sumproduct.html

 

[kuldeepjainesl]

Thanks Luke M.


I will go through all of these to raise my sum product knowledge. i also have found artical at http://chandoo.org/wp/2011/05/26/advanced-sumproduct-queries/ at chandoo which seems to be more fit for advance examples.


Regards,

Kuldeep