Excel 2003 Equivalent formula for CountIFS

H

healey21

Well-known Member
Joined
Dec 22, 2009
Messages
900
I am currently using this formula to count records with several conditions in Excel 2007. I have now been told that there are users who only have 2003 so the CountIFS wont work.

Which formula can I use in Excel 2003.

Sample formula is

=COUNTIFS(Sheet1!$E:$E,$A3,Sheet1!$J:$J,C$2)

Sample of Sheet 1

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 37px"><COL style="WIDTH: 130px"><COL style="WIDTH: 92px"><COL style="WIDTH: 75px"><COL style="WIDTH: 40px"><COL style="WIDTH: 75px"><COL style="WIDTH: 32px"><COL style="WIDTH: 48px"><COL style="WIDTH: 37px"><COL style="WIDTH: 143px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">ID</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">AT</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Shop Type</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">LOCATION</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Area</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">DURATION</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Day</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Month</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Year</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Date</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right">1</TD><TD style="TEXT-ALIGN: right">31/03/2011 11:27:32</TD><TD>Sports</TD><TD>Leeds</TD><TD>Front</TD><TD style="TEXT-ALIGN: right">00:00:01</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">31</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">3</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">31</TD><TD style="TEXT-ALIGN: right">31 Mar 2011</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">31/03/2011 11:32:55</TD><TD>Groceries</TD><TD>Manchester</TD><TD>Back</TD><TD style="TEXT-ALIGN: right">00:00:11</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">31</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">3</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">31</TD><TD style="TEXT-ALIGN: right">31 Mar 2011</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">31/03/2011 11:33:27</TD><TD>Sports</TD><TD>Bristol</TD><TD>Front</TD><TD style="TEXT-ALIGN: right">00:00:05</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">31</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">3</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">31</TD><TD style="TEXT-ALIGN: right">31 Mar 2011</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">31/03/2011 11:34:42</TD><TD>Groceries</TD><TD>Birmingham</TD><TD>Hall</TD><TD style="TEXT-ALIGN: right">00:00:36</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">31</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">3</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">31</TD><TD style="TEXT-ALIGN: right">31 Mar 2011</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: right">5</TD><TD style="TEXT-ALIGN: right">31/03/2011 11:34:57</TD><TD>Stationery</TD><TD>Leeds</TD><TD>Hall</TD><TD style="TEXT-ALIGN: right">00:00:05</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">31</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">3</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">31</TD><TD style="TEXT-ALIGN: right">31 Mar 2011</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: right">6</TD><TD style="TEXT-ALIGN: right">31/03/2011 11:35:51</TD><TD>Stationery</TD><TD>Manchester</TD><TD>Front</TD><TD style="TEXT-ALIGN: right">00:00:46</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">31</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">3</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">31</TD><TD style="TEXT-ALIGN: right">31 Mar 2011</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: right">7</TD><TD style="TEXT-ALIGN: right">31/03/2011 11:39:23</TD><TD>Sports</TD><TD>Manchester</TD><TD>Back</TD><TD style="TEXT-ALIGN: right">00:00:05</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">31</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">3</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">31</TD><TD style="TEXT-ALIGN: right">31 Mar 2011</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: right">8</TD><TD style="TEXT-ALIGN: right">31/03/2011 11:39:40</TD><TD>Groceries</TD><TD>Bristol</TD><TD>Hall</TD><TD style="TEXT-ALIGN: right">00:00:10</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">31</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">3</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">31</TD><TD style="TEXT-ALIGN: right">31 Mar 2011</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>G2</TD><TD>=DAY(B2)</TD></TR><TR><TD>H2</TD><TD>=MONTH(B2)</TD></TR><TR><TD>J2</TD><TD>=TEXT(B2,"dd mmm yyyy")</TD></TR><TR><TD>G3</TD><TD>=DAY(B3)</TD></TR><TR><TD>H3</TD><TD>=MONTH(B3)</TD></TR><TR><TD>J3</TD><TD>=TEXT(B3,"dd mmm yyyy")</TD></TR><TR><TD>G4</TD><TD>=DAY(B4)</TD></TR><TR><TD>H4</TD><TD>=MONTH(B4)</TD></TR><TR><TD>J4</TD><TD>=TEXT(B4,"dd mmm yyyy")</TD></TR><TR><TD>G5</TD><TD>=DAY(B5)</TD></TR><TR><TD>H5</TD><TD>=MONTH(B5)</TD></TR><TR><TD>J5</TD><TD>=TEXT(B5,"dd mmm yyyy")</TD></TR><TR><TD>G6</TD><TD>=DAY(B6)</TD></TR><TR><TD>H6</TD><TD>=MONTH(B6)</TD></TR><TR><TD>J6</TD><TD>=TEXT(B6,"dd mmm yyyy")</TD></TR><TR><TD>G7</TD><TD>=DAY(B7)</TD></TR><TR><TD>H7</TD><TD>=MONTH(B7)</TD></TR><TR><TD>J7</TD><TD>=TEXT(B7,"dd mmm yyyy")</TD></TR><TR><TD>G8</TD><TD>=DAY(B8)</TD></TR><TR><TD>H8</TD><TD>=MONTH(B8)</TD></TR><TR><TD>J8</TD><TD>=TEXT(B8,"dd mmm yyyy")</TD></TR><TR><TD>G9</TD><TD>=DAY(B9)</TD></TR><TR><TD>H9</TD><TD>=MONTH(B9)</TD></TR><TR><TD>J9</TD><TD>=TEXT(B9,"dd mmm yyyy")</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
Click to expand...

Sample of Sheet 2

Sheet2

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 75px"><COL style="WIDTH: 75px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD> </TD><TD> </TD><TD>Date</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Area</TD><TD>Totals</TD><TD style="TEXT-ALIGN: right">31/03/2011</TD><TD style="TEXT-ALIGN: right">01/04/2011</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>Front</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>Back</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD>Front</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD>Hall</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD>Hall</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">0</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B3</TD><TD>=COUNTIF(Sheet1!E:E,A3)</TD></TR><TR><TD>C3</TD><TD>=COUNTIFS(Sheet1!$E:$E,$A3,Sheet1!$J:$J,C$2)</TD></TR><TR><TD>D3</TD><TD>=COUNTIFS(Sheet1!$E:$E,$A3,Sheet1!$J:$J,D$2)</TD></TR><TR><TD>B4</TD><TD>=COUNTIF(Sheet1!E:E,A4)</TD></TR><TR><TD>C4</TD><TD>=COUNTIFS(Sheet1!$E:$E,$A4,Sheet1!$J:$J,C$2)</TD></TR><TR><TD>D4</TD><TD>=COUNTIFS(Sheet1!$E:$E,$A4,Sheet1!$J:$J,D$2)</TD></TR><TR><TD>B5</TD><TD>=COUNTIF(Sheet1!E:E,A5)</TD></TR><TR><TD>C5</TD><TD>=COUNTIFS(Sheet1!$E:$E,$A5,Sheet1!$J:$J,C$2)</TD></TR><TR><TD>D5</TD><TD>=COUNTIFS(Sheet1!$E:$E,$A5,Sheet1!$J:$J,D$2)</TD></TR><TR><TD>B6</TD><TD>=COUNTIF(Sheet1!E:E,A6)</TD></TR><TR><TD>C6</TD><TD>=COUNTIFS(Sheet1!$E:$E,$A6,Sheet1!$J:$J,C$2)</TD></TR><TR><TD>D6</TD><TD>=COUNTIFS(Sheet1!$E:$E,$A6,Sheet1!$J:$J,D$2)</TD></TR><TR><TD>B7</TD><TD>=COUNTIF(Sheet1!E:E,A7)</TD></TR><TR><TD>C7</TD><TD>=COUNTIFS(Sheet1!$E:$E,$A7,Sheet1!$J:$J,C$2)</TD></TR><TR><TD>D7</TD><TD>=COUNTIFS(Sheet1!$E:$E,$A7,Sheet1!$J:$J,D$2)</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
Click to expand...
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Sort by date Sort by votes
Thank you Peter,

I get a result of Zero but i think this might be due to the date field being checked is a text calculation of

=TEXT(B2,"dd mmm yyyy")

Would that make a difference or is there a way to do it on the date and time column, ignoring the time?

Very grateful for your reply.
 
Upvote 0
Perhaps with a date in C2

=SUMPRODUCT(--(INT(Sheet1!$B1:$B1000)=C$2),--(Sheet1!$E1:$E1000=$A3))
 
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
623
smozgur
smozgur
E
  • Question Question
VBA code clean up
Replies
9
Views
583
mumps
mumps
D
  • Question Question
Looking for a way to allocate shifts between teams, maybe a LET formula
Replies
0
Views
90
Dazzybeeguy
D
E
  • Question Question
Adding a custom message.
Replies
1
Views
666
Micron
M
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C

Forum statistics

Threads
1,224,603
Messages
6,179,850
Members
452,948
Latest member
UsmanAli786

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