Prev: VBA data validation
Next: COUNT unique letter in a column
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 validation
Next: COUNT unique letter in a column