Countif & Sumif

[DOF2001]

Hi, does anyone know if there is possible to do a sumif and or count if based on more than one criteria. ex:

sumif(range, and(criteria1,criteria2),sumRange)
countif(range, and(criteria1, criteria2))


i try to do like above but it is not working, Is this possible??


Thank you!

 

[VoG]

Try

sumif(range, criteria1,sumRange) + sumif(range, criteria2,sumRange)

countif(range, criteria1) + countif(range, criteria2)

Or take a look at sumproduct.

 

[jeffreybrown]

In 2007 you can use Sumifs, but for pre...something like this.

Sheet222

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 89px"><COL style="WIDTH: 89px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>J</TD><TD>K</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">21</TD><TD style="TEXT-ALIGN: center">A</TD><TD style="TEXT-ALIGN: center">1</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">22</TD><TD style="TEXT-ALIGN: center">B</TD><TD style="TEXT-ALIGN: center">2</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">23</TD><TD style="TEXT-ALIGN: center">A</TD><TD style="TEXT-ALIGN: center">3</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">24</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">25</TD><TD style="TEXT-ALIGN: right">6</TD><TD> </TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>J25</TD><TD>=SUMPRODUCT(--(J21:J23="A")+(J21:J23="B"),K21:K23)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

 

[gecs]

Search the forum for array formulas like: {=SUM(IF...)} for SUM on multiple criteria and SUMPRODUCT with double unary operators used to apply more criteria for both counting and summing the records. I'm sure you can find a lot of examples to adapt to your situation. Another alternative would be to post an example of your data together with a description of what you're trying to achieve and somebody will help you with the formula. So, the answer to your first question is: yes, is possible.

 

[Aladin Akyurek]

Hi, does anyone know if there is possible to do a sumif and or count if based on more than one criteria. ex:

sumif(range, and(criteria1,criteria2),sumRange)
countif(range, and(criteria1, criteria2))


i try to do like above but it is not working, Is this possible??


Thank you!

Set up examples...

=SUM(SUMIF($A$2:$A$100,{"X","Y"},$B$2:$B$100))

=SUM(COUNTIF($A$2:$A$100,{"X","Y"}))

=SUMPRODUCT(SUMIF($A$2:$A$100,CriteriaRange,$B$2:$B$100))

=SUMPRODUCT(COUNTIF($A$2:$A$100,CriteriaRange))

 

[DOF2001]

Thanks for the fast response but that is not exactly what i am looking for. let me post an example

• 
  Country Class of Service Name Low Fare Lost
  USA ECONOMY 294.32 0
  USA ECONOMY 300.5 0
  USA ECONOMY 291.5 0
  CAN ECONOMY 396.15 -28.27
  CAN ECONOMY 267.72 -66.33
  USA ECONOMY 149.37 -100.63
  CAN ECONOMY 571.95 -276.27
  CAN ECONOMY 369 -299.67
  USA ECONOMY 377 -1145.71
  USA ECONOMY 219.5 -27.69
  USA ECONOMY 719 -33.19
  USA ECONOMY 109.5 -52.62
  USA ECONOMY 440.74 -53.76
  USA ECONOMY 1799.96 -55.04
  USA ECONOMY 228.6 -55.6
  USA ECONOMY 213 -56

so in this case i need to count hte number of records that show in column A "USA" and column D "0" in this case 3

sumif: column A "USA" and column D "0" sum column C in this case 886.32

 

[DOF2001]

sorry here is a better table:

Excel Workbook
	A	B	C	D	E	F
1	Country	Class of Service Name	Low Fare	Lost	Days Adv	Booking Type
2	USA	ECONOMY	294.32	0.00	6	AGENCY
3	USA	ECONOMY	300.50	0.00	15	AGENCY
4	USA	ECONOMY	291.50	0.00	14	AGENCY
5	CAN	ECONOMY	396.15	-28.27	18	AGENCY
6	CAN	ECONOMY	267.72	-66.33	6	AGENCY
7	USA	ECONOMY	149.37	-100.63	12	AGENCY
8	CAN	ECONOMY	571.95	-276.27	13	AGENCY
9	CAN	ECONOMY	369.00	-299.67	7	AGENCY
10	USA	ECONOMY	377.00	-1,145.71	4	AGENCY
11	USA	ECONOMY	219.50	-27.69	12	ONLINE
12	USA	ECONOMY	719.00	-33.19	14	ONLINE
13	USA	ECONOMY	109.50	-52.62	14	ONLINE
14	USA	ECONOMY	440.74	-53.76	10	ONLINE
15	USA	ECONOMY	1,799.96	-55.04	4	ONLINE
16	USA	ECONOMY	228.60	-55.60	1	ONLINE
17	USA	ECONOMY	213.00	-56.00	7	ONLINE
Sheet1

 

[jeffreybrown]

Try

Excel Workbook
	H
2	3
3	886.32
Sheet3

 

[gecs]

Count: =SUMPRODUCT(--(A2:A17="USA"),--(D2:D17=0))
Sum: =SUMPRODUCT(--(A2:A17="USA"),--(D2:D17=0),(C2:C17))

 

[DOF2001]

Thanks a lot to all, that was just what i was looing for.