From: Jessejames on 8 May 2010 05:15 A B C D E 1 col tot subtot1 subtot2 DISC 2 black 12 1 11 AA 3 white 14 2 12 BB 4 blue 16 3 13 CC 5 blue 18 4 14D DD QUERY1: (COLUMN A TO D ONLY, COLUMN E WILL BE USED ON QUERY2) 8 blue 34 query 1 states that if i type black (A8) the response of the B8 will be 34 using the formula below ( 16+18) =SUMIF(A2:A5,A8,B2:B5) query 1 has no problem and query 2 will follow..... but i added one column E (disc) and as we can see i need to get only the value for column E with CC disc ONLY, it should be 16 only. please help and put the exact formula. tnx =) ================== -- xxxJessexxx From: Ms-Exl-Learner on 8 May 2010 05:50 Try thisâ€¦ Excel 2003:- =SUMPRODUCT((A2:A5=A8)*(E2:E5="CC"),B2:B5) Excel 2007:- =SUMIFS(B2:B5,A2:A5,A8,E2:E5,"CC") In your example you have mentioned the A8 cell value as Black but I hope that it should be Blue. Remember to Click Yes, if this post helps! -------------------- (Ms-Exl-Learner) -------------------- "Jessejames" wrote: > A B C D > E > 1 col tot subtot1 subtot2 DISC > 2 black 12 1 11 AA > 3 white 14 2 12 BB > 4 blue 16 3 13 CC > 5 blue 18 4 14D DD > > QUERY1: (COLUMN A TO D ONLY, COLUMN E WILL BE USED ON QUERY2) > 8 blue 34 > > query 1 states that if i type black (A8) the response of the B8 will be 34 > using the formula below ( 16+18) > =SUMIF(A2:A5,A8,B2:B5) > > query 1 has no problem > and query 2 will follow..... > > but i added one column E (disc) > > and as we can see i need to get only the value for column E with CC disc > ONLY, it should be 16 only. please help and put the exact formula. tnx > > =) > > ================== > -- > xxxJessexxx  |  Pages: 1 Prev: VBA data validationNext: COUNT unique letter in a column