I Need Help to SUM Data base on criteria

  • Thread starter Thread starter bazish
  • Start date Start date
B

bazish

Hello Friends
I need urgent help regarding sum function..
my problem is i have three column containg data..

A B C D
1 sam Red 500 *sum of C column only when A and
2 sat Blue 250 B equal to Sam and Red
3 sam Red 360
4 nic Green 290 * Sum of C Column only when A
5 nic Yellow 480 and B equal to sat and blue
6 sam green 269
7 nic yellow 480 *sum of C Column only when A
8 sat blue 158 and B equal to nic and
yellow


if you understand my problem please solve this for me . i have tired a
lot but i have to say i cant make my self to solve this problem.. if
anyone help me regarding my this problem i will be very thankfull to
him...
please if you need to ask me question about this problem please email
me on my address .. i am waiting very badly solution for this proble..



------------------------------------------------




------------------------------------------------
 
The following is an explanation of how an array formula using SUMPRODUCT works. You can adjust
the formula to do what you want with your data. As an example, your first formula for Sam and Red
would be as follows:-

=SUMPRODUCT((A1:A20="Sam")*(B1:B20="Red")*(C1:C20)) with hardwired criteria, or


=SUMPRODUCT((A1:A20=Z1)*(B1:B20=Z2)*(C1:C20)) with criteria in other cells (Z1 = "Sam" & Z2 =
"Red")


The formula exploits the fact that Excel interprets TRUE as 1 and FALSE
as 0. Take the formula below:-

=SUMPRODUCT((A9:A20=A1)*(B9:B20="A")*(C9:C20))

This sets up an array that gives you something that looks like this
(depending on the variables of course):-

A B C
9 TRUE * FALSE * 3
10 FALSE * FALSE * 4
11 TRUE * TRUE * 2
12 TRUE * TRUE * 1
13 TRUE * FALSE * 4
14 TRUE * TRUE * 3
15 TRUE * TRUE * 2
16 FALSE * TRUE * 8
17 TRUE * TRUE * 6
18 TRUE * TRUE * 8
19 TRUE * TRUE * 7
20 TRUE * TRUE * 6

Which because TRUE=1 and FALSE=0, is interpreted as:-

A B C
9 1 * 0 * 3 = 0
10 0 * 0 * 4 = 0
11 1 * 1 * 2 = 2
12 1 * 1 * 1 = 1
13 1 * 0 * 4 = 0
14 1 * 1 * 3 = 3
15 1 * 1 * 2 = 2
16 0 * 1 * 8 = 0
17 1 * 1 * 6 = 6
18 1 * 1 * 8 = 8
19 1 * 1 * 7 = 7
20 1 * 1 * 6 = 6
-------------
35

and the SUM bit just adds up all the end values of the products



If you exclude the last part of the formula, so that it becomes:-

=SUMPRODUCT((A9:A20=A1)*(B9:B20="A"))

then what you end up with is a sum of a bunch of TRUE/FALSE values depending on whether or not the
criteria has been met on that row, and this is the same as counting the number of records that
meet your criteria. Imagine the above tables without Column C, and the last one would look like
the following:-

A B
9 1 * 0 = 0
10 0 * 0 = 0
11 1 * 1 = 1
12 1 * 1 = 1
13 1 * 0 = 0
14 1 * 1 = 1
15 1 * 1 = 1
16 0 * 1 = 0
17 1 * 1 = 1
18 1 * 1 = 1
19 1 * 1 = 1
20 1 * 1 = 1
 
thanks a lot solving my problem
i never know if i can get this solve this much soon
very much thankfull for your help
i was using sumif function to solve this . but the way you solve my
problem i hope in future you will be here to solve my problem..
once again thanks



------------------------------------------------




------------------------------------------------
 
yes dear thats what i need and thanks for asking .. dear i already solve
my problem here from user Ken Wright you can read his reply but if you
have also some kind of solution please tell me thats too..
thanks



------------------------------------------------




------------------------------------------------
 
My pleasure. Glad you got sorted, and we always appreciate feedback as to how you got on in here,
so thanks.
 
MVP as follows:-



Sys Spec simply says what versions of software I am working with. If I give a solution then I
normally try it in both versions of Excel that I have, but I wouldn't have tried it in 97 for
example.
 
Back
Top