Formula Help

  • Thread starter Thread starter Dave
  • Start date Start date
D

Dave

I have a spreadsheet where all entries in Col A are either "E" or "S". Col B
has 10 different possible entries. I need a formula to 1st, select every row
that is an "E", then 2nd, out of that, select only the rows that match a
criteria for Col B's possibilites and 3rd, using the rows that the 2nd step
selected, add all of the values in Col C and generate a total.
I have various modifications of IF and SUMIF and cannot get anything to work.
Any help would be greatly appreciated.
 
Say the data looks like:

E 1 88
S B 17
E C 89
S C 39
E A 50
S E 31
E 4 100
E 2 74
S 1 64
S B 98
E A 48
E 1 81
E D 81
S D 71
S C 44
S 3 26
E 1 77
S A 54
E 2 91
E 1 54
E D 57
S E 64
S D 29
E 2 31
S C 44
S 1 65
E A 19
E B 73
S D 78
E E 23

and we want the sum of column C for column A=E and column B=1.

=SUMPRODUCT(--(A1:A100="E"),--(B1:B100=1),(C1:C100))

will display 300
 
I have a spreadsheet where all entries in Col A are either "E" or "S".  Col B
has 10 different possible entries.  I need a formula to 1st, select every row
that is an "E", then 2nd, out of that, select only the rows that match a
criteria for Col B's possibilites and 3rd, using the rows that the 2nd step
selected, add all of the values in Col C and generate a total.
I have various modifications of IF and SUMIF and cannot get anything to work.
Any help would be greatly appreciated.
Click to expand...

First post on a new thread as you might be ignored sending your
question on others.
Second the best function to answer multiple conditioning is sumproduct

Sumproduct(--(A1:A10="E"),--(B1:B10="put your criteria for column B
here",C1:C10)

For more info on sumproduct, go to
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
 
Thank You! That worked great!

Gary''s Student said:
Say the data looks like:

E 1 88
S B 17
E C 89
S C 39
E A 50
S E 31
E 4 100
E 2 74
S 1 64
S B 98
E A 48
E 1 81
E D 81
S D 71
S C 44
S 3 26
E 1 77
S A 54
E 2 91
E 1 54
E D 57
S E 64
S D 29
E 2 31
S C 44
S 1 65
E A 19
E B 73
S D 78
E E 23

and we want the sum of column C for column A=E and column B=1.

=SUMPRODUCT(--(A1:A100="E"),--(B1:B100=1),(C1:C100))

will display 300
Click to expand...
 
Hi,

The best solution is always a matter of some issue - for example, if you
arre using 2007 your best solution might be

=SUMIFS(C1:C99,A1:A99,"E",B1:B99,15)

where you want to sum column C if A is E and B is 15.
 
Back
Top