• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

sum

S

sdsurzh

Member
Hi,


Please refer the below below sheet and give me a solution. (I want the result column to be filled by formula)


http://www.2shared.com/file/AICNqcSJ/Book7.html


Thanks,

Suresh Kumar S
 
Good day Suresh Kumar


Youe title does not give much in the way of a discription as to what your problem is, if you look at other post titles you will understand.


In your work sheet you have two columns showing the same data Result and Count, result and count of what ?
 
Thanks Bobhc,


I want the column F to be updated by formula with checking with column I to O comparing with A to F. Now i have updated the information in the below sheet.


http://www.2shared.com/file/T488mAHI/Book7.html


Thanks,

Suresh Kumar S
 
Hi Suresh & bobhc,


@ Suresh,


I think there is problem is your data that you want to use. The Original data in J1:Q27 uses both "Petrol & Diesel" for "Cars" and "Diesel" for buses, but the Data in A1:E27 shows only "Petrol" for car and for cars Col O cites them to be private and buses to be Public what is converse in you data in A1:E27. So that is a contradiction.


I changed your final data in J1:Q27 to following:

[pre]
Code: 
CCC	DDD	AAA	AAA	BBB	BBB	Year	Count
Car	travel	petrol			Public	2000	12
Car	travel	petrol			Public	2001	13
Car	travel	petrol			Public	2002	14
Car	travel	petrol			Public	2003	15
Car	travel	petrol			Public	2004	16
Car	travel	petrol			Public	2005	17
Car	travel	petrol			Public	2006	18
Car	travel	petrol			Public	2007	19
Car	travel	petrol			Public	2008	20
Car	travel	petrol			Public	2009	21
Car	travel	petrol			Public	2010	22
Car	travel	petrol			Public	2011	23
Car	travel	petrol			Public	2012	24
Bus	travel		Diesel	Private		2000	2
Bus	travel		Diesel	Private		2001	3
Bus	travel		Diesel	Private		2002	4
Bus	travel		Diesel	Private		2003	5
Bus	travel		Diesel	Private		2004	6
Bus	travel		Diesel	Private		2005	7
Bus	travel		Diesel	Private		2006	8
Bus	travel		Diesel	Private		2007	9
Bus	travel		Diesel	Private		2008	10
Bus	travel		Diesel	Private		2009	11
Bus	travel		Diesel	Private		2010	12
Bus	travel		Diesel	Private		2011	13
Bus	travel		Diesel	Private		2012	14[/pre]
and applied this formula in G1, Press Ctrl+Shift+Enter and drag down:


`

=INDEX($Q$2:$Q$27,SUM(IF(($J$2:$J$27=A2)*($K$2:$K$27=B2)*(TRIM($L$2:$L$27&$M$2:$M$27)=C2)*(TRIM($N$2:$N$27&$O$2:$O$27)=D2)*($P$2:$P$27=F2),ROW($A$1:$A$26)),0))


Hopefully it helps.


Faseeh
 
Dear Faseeh Thanks,


Can you please check my 2 download link file and suggest a formula. As per the above i am getting 12 for all rows. (Is it possible to upload the workbook )


Thanks,

Suresh Kumar S
 
Hi Suresh,


Let me check your latest upload, but this formula is working for me here.


Please press Ctrl+Shift+Enter to execute this formula and drag down.


Edit: Can't access file server due to admin restriction just now msg appeared on my computer scree, so please check or/and after that if problem exists email it to faseeh10@hotmail.com


Regards,
 
You must log in or register to reply here.
Back
Top