# Countif & Sumif

#### DOF2001

##### Active Member
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!

### Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Try

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

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

Or take a look at sumproduct.

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

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.

Last edited:
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))

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

sorry here is a better table:
Excel Workbook
ABCDEF
1CountryClass of Service NameLow FareLostDays AdvBooking Type
2USAECONOMY294.320.006AGENCY
3USAECONOMY300.500.0015AGENCY
4USAECONOMY291.500.0014AGENCY
5CANECONOMY396.15-28.2718AGENCY
6CANECONOMY267.72-66.336AGENCY
7USAECONOMY149.37-100.6312AGENCY
8CANECONOMY571.95-276.2713AGENCY
9CANECONOMY369.00-299.677AGENCY
10USAECONOMY377.00-1,145.714AGENCY
11USAECONOMY219.50-27.6912ONLINE
12USAECONOMY719.00-33.1914ONLINE
13USAECONOMY109.50-52.6214ONLINE
14USAECONOMY440.74-53.7610ONLINE
15USAECONOMY1,799.96-55.044ONLINE
16USAECONOMY228.60-55.601ONLINE
17USAECONOMY213.00-56.007ONLINE
Sheet1

Try

Excel Workbook
H
23
3886.32
Sheet3

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

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

Replies
1
Views
39
Replies
5
Views
491
Replies
2
Views
411
Replies
1
Views
182
Replies
9
Views
1K

1,203,620
Messages
6,056,335
Members
444,861
Latest member
B4you_Andrea

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back