Multiple criteria for Sumifs() & countifs() in Excel 2007

A

acc16

New Member
Joined
Aug 8, 2010
Messages
12
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p> </o:p>
Hi,<o:p></o:p>
<o:p> </o:p>
I am trying to consolidate a report in which I need to get the sum & count for couple of rows.<o:p></o:p>
<o:p> </o:p>
Col A = Country Code<o:p></o:p>
Col B = Table<o:p></o:p>
Col C = Field Name<o:p></o:p>
Col D = Field 1<o:p></o:p>
Col E = Message<o:p></o:p>
Col F = Desc<o:p></o:p>
<o:p> </o:p>
<TABLE style="MARGIN: auto auto auto -0.75pt; WIDTH: 416.55pt; BORDER-COLLAPSE: collapse; mso-yfti-tbllook: 1184; mso-padding-alt: 0in 5.4pt 0in 5.4pt" class=MsoNormalTable border=0 cellSpacing=0 cellPadding=0 width=555><TBODY><TR style="HEIGHT: 15pt; mso-yfti-irow: 0; mso-yfti-firstrow: yes"><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; PADDING-LEFT: 5.4pt; WIDTH: 48pt; PADDING-RIGHT: 5.4pt; BACKGROUND: yellow; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" vAlign=bottom width=64 noWrap>Country<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; PADDING-LEFT: 5.4pt; WIDTH: 48pt; PADDING-RIGHT: 5.4pt; BACKGROUND: yellow; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" vAlign=bottom width=64 noWrap>Table<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; PADDING-LEFT: 5.4pt; WIDTH: 59pt; PADDING-RIGHT: 5.4pt; BACKGROUND: yellow; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" vAlign=bottom width=79 noWrap>Field Name<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; PADDING-LEFT: 5.4pt; WIDTH: 48pt; PADDING-RIGHT: 5.4pt; BACKGROUND: yellow; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" vAlign=bottom width=64 noWrap>Field 1<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; PADDING-LEFT: 5.4pt; WIDTH: 100.55pt; PADDING-RIGHT: 5.4pt; BACKGROUND: yellow; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" vAlign=bottom width=134 noWrap>Message<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; PADDING-LEFT: 5.4pt; WIDTH: 65pt; PADDING-RIGHT: 5.4pt; BACKGROUND: yellow; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" vAlign=bottom width=87 noWrap>Desc<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; PADDING-LEFT: 5.4pt; WIDTH: 48pt; PADDING-RIGHT: 5.4pt; BACKGROUND: yellow; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" vAlign=bottom width=64 noWrap>SumChk<o:p></o:p>
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 1"><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 48pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" vAlign=bottom width=64 noWrap>
10<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 48pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" vAlign=bottom width=64 noWrap>Table 1<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 59pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" vAlign=bottom width=79 noWrap>AIRC<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 48pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" vAlign=bottom width=64 noWrap></TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 100.55pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" vAlign=bottom width=134 noWrap>No rule found<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 65pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" vAlign=bottom width=87 noWrap>Rule Error<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 48pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" vAlign=bottom width=64 noWrap>
3<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 2"><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 48pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" vAlign=bottom width=64 noWrap>
12<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 48pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" vAlign=bottom width=64 noWrap>Table 2<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 59pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" vAlign=bottom width=79 noWrap>BOCJ<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 48pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" vAlign=bottom width=64 noWrap>DIDW<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 100.55pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" vAlign=bottom width=134 noWrap>Data Type miss-match<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 65pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" vAlign=bottom width=87 noWrap>Data Error<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 48pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" vAlign=bottom width=64 noWrap>
1<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 3"><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 48pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" vAlign=bottom width=64 noWrap>
14<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 48pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" vAlign=bottom width=64 noWrap>Table 1<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 59pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" vAlign=bottom width=79 noWrap>AIRC<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 48pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" vAlign=bottom width=64 noWrap>
2<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 100.55pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" vAlign=bottom width=134 noWrap>No rule found<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 65pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" vAlign=bottom width=87 noWrap>Rule Error<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 48pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" vAlign=bottom width=64 noWrap>
1<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 4"><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 48pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" vAlign=bottom width=64 noWrap>
10<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 48pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" vAlign=bottom width=64 noWrap>Table 3<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 59pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" vAlign=bottom width=79 noWrap>BOCJ<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 48pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" vAlign=bottom width=64 noWrap>
3<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 100.55pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" vAlign=bottom width=134 noWrap>No rule found<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 65pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" vAlign=bottom width=87 noWrap>Rule Error<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 48pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" vAlign=bottom width=64 noWrap>
1<o:p></o:p>​
</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 5; mso-yfti-lastrow: yes"><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 48pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" vAlign=bottom width=64 noWrap>
12<o:p></o:p>​
</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 48pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" vAlign=bottom width=64 noWrap>Table 1<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 59pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" vAlign=bottom width=79 noWrap>AIRC<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 48pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" vAlign=bottom width=64 noWrap></TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 100.55pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" vAlign=bottom width=134 noWrap>Data Type miss-match<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 65pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" vAlign=bottom width=87 noWrap>Data Error<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 48pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" vAlign=bottom width=64 noWrap>
6<o:p></o:p>​
</TD></TR></TBODY></TABLE>
<o:p> </o:p>
<o:p> </o:p>
I want to get the no. of records for the following criterias. <o:p></o:p>
<o:p> </o:p>
Sum of Col G (SumChk) - Country = 10 & 12 with Table = Table 1 & Table 2 with Field 1 = Blank and Desc = Rule Error & Data Error<o:p></o:p>
<o:p> </o:p>
Result should be 9.<o:p></o:p>
<o:p> </o:p>
<o:p> </o:p>
<o:p> </o:p>
Count of Col G (SumChk) - Country = 10 & 12 with Table = Table 1 & Table 2 with Field 1 = Blank and Desc = Rule Error & Data Error<o:p></o:p>
<o:p> </o:p>
Result should be 2.<o:p></o:p>
<o:p> </o:p>
<o:p> </o:p>
Any help is appreciated.<o:p></o:p>
<o:p> </o:p>
Thanks in advance.<o:p></o:p>
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Sort by date Sort by votes
Try...
Code: 
=SUM(SUMIFS($G$2:$G$6,$A$2:$A$6,{10,12},$B$2:$B$6,{"Table 1","Table 2"},
    $D$2:$D$6,"=",$F$2:$F$6,{"Rule Error","Data Error"}))

Code: 
=SUM(COUNTIFS($G$2:$G$6,"<>",$A$2:$A$6,{10,12},$B$2:$B$6,{"Table 1","Table 2"},
    $D$2:$D$6,"=",$F$2:$F$6,{"Rule Error","Data Error"}))
 
Last edited:
Upvote 0
One way to do it is with a SUMPRODUCT formula.
Excel Workbook
ABCDEFG
1CountryTableField NameField 1MessageDescSumChk
210Table 1AIRCNo rule foundRule Error3
312Table 2BOCJDIDWData Type miss-matchData Error1
414Table 1AIRC2No rule foundRule Error1
510Table 3BOCJ3No rule foundRule Error1
612Table 1AIRCData Type miss-matchData Error6
7
8Sum9
9Count2
...
Cell Formulas
RangeFormula
B8=SUMPRODUCT(((A2:A6=10)+(A2:A6=12))*((B2:B6="Table 1")+(B2:B6="Table 2"))*(D2:D6="")*((F2:F6="Rule Error")+(F2:F6="Data Error"))*(G2:G6))
B9=SUMPRODUCT(((A2:A6=10)+(A2:A6=12))*((B2:B6="Table 1")+(B2:B6="Table 2"))*(D2:D6="")*((F2:F6="Rule Error")+(F2:F6="Data Error")))


The Plus symbol in this formula acts like an OR function.
 
Upvote 0
Thank you Aladin Akyurek.

I tried the Sumif() as suggested.

=SUM(SUMIFS($G$2:$G$6,$A$2:$A$6,{10,12},$B$2:$B$6,{"Table 1","Table 2"},
$D$2:$D$6,"=",$F$2:$F$6,{"Rule Error","Data Error"}))

The result which I got was Sum = 3 & Count = 1

Do I need to activate any addins in order to get this?

====================================================
Thank you AlphaFrog.

Although the Sumproduct() is a bit complex formula, but I was able to get the desired result.
Is there any simpler way of by using sumifs() or countifs() ???
 
Upvote 0
acc16 said:
Thank you Aladin Akyurek.

I tried the Sumif() as suggested.

=SUM(SUMIFS($G$2:$G$6,$A$2:$A$6,{10,12},$B$2:$B$6,{"Table 1","Table 2"},
$D$2:$D$6,"=",$F$2:$F$6,{"Rule Error","Data Error"}))

The result which I got was Sum = 3 & Count = 1

Do I need to activate any addins in order to get this?

====================================================
Thank you AlphaFrog.

Although the Sumproduct() is a bit complex formula, but I was able to get the desired result.
Is there any simpler way of by using sumifs() or countifs() ???
Click to expand...

SumIfs/CountIfs cannot work with muliple ranges each subjected to multiple criteria. I just forgat that rule... We need an array-processing formula here like SumProduct. Another way to set up such a formula is with IsNumber/Match instead of using the + operaror:

Code: 
=SUMPRODUCT(
   $G$2:$G$6,
   --ISNUMBER(MATCH($A$2:$A$6,{10,12},0)),
   --ISNUMBER(MATCH($B$2:$B$6,{"Table 1","Table 2"},0)),
   --($D$2:$D$6=""),
   --ISNUMBER(MATCH($F$2:$F$6,{"Rule Error","Data Error"},0)))

Code: 
=SUMPRODUCT(
   --ISNUMBER($G$2:$G$6),
   --ISNUMBER(MATCH($A$2:$A$6,{10,12},0)),
   --ISNUMBER(MATCH($B$2:$B$6,{"Table 1","Table 2"},0)),
   --($D$2:$D$6=""),
   --ISNUMBER(MATCH($F$2:$F$6,{"Rule Error","Data Error"},0)))
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
537
smozgur
smozgur
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
E
  • Question Question
VBA for Web Scraping help
Replies
0
Views
902
epactheactor
E
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C

Forum statistics

Threads
1,223,243
Messages
6,170,967
Members
452,371
Latest member
Frana

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top