If statement and Count

R

r!chg

New Member
Joined
Mar 14, 2011
Messages
13
Hi All...

Im trying to get count and if to work.

Column F in sheet Data_2011_Day is a continuous column of data. For Jan and Feb in Cell C3 I get the correct count. But For March I get a count of 36. I need the Count for Mar to be 35. I only get this if I change the range for Count to be f62:f95. But that means I miss a row of data. f61?

=IF('MTD (2)'!$C$3="Jan",COUNT(Data_2011_Day!$F$5:$F$32),IF('MTD (2)'!$C$3="Feb",COUNT(Data_2011_Day!$F$33:$F$60),IF('MTD (2)'!$C$3="Mar",COUNT(Data_2011_Day!$F$62:$F$95,

Any reason why it would do this?
 
Sort by date Sort by votes
Based on information so far I would think on the lines of

<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: 75px"><COL style="WIDTH: 85px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><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><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD> </TD><TD>Date</TD><TD>random data</TD><TD> </TD><TD>count </TD><TD>sum </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD> </TD><TD style="TEXT-ALIGN: right">01/01/2011</TD><TD style="TEXT-ALIGN: right">0.227634717</TD><TD> </TD><TD style="TEXT-ALIGN: right">4</TD><TD style="TEXT-ALIGN: right">1.42315</TD><TD>Jan</TD><TD style="TEXT-ALIGN: right">02/01/2011</TD><TD style="TEXT-ALIGN: right">29/01/2011</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>Jan</TD><TD style="TEXT-ALIGN: right">02/01/2011</TD><TD style="TEXT-ALIGN: right">0.233796476</TD><TD> </TD><TD> </TD><TD> </TD><TD>Feb</TD><TD style="TEXT-ALIGN: right">30/01/2011</TD><TD style="TEXT-ALIGN: right">26/02/2011</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD> </TD><TD style="TEXT-ALIGN: right">09/01/2011</TD><TD style="TEXT-ALIGN: right">0.280080133</TD><TD> </TD><TD> </TD><TD> </TD><TD>Mar</TD><TD style="TEXT-ALIGN: right">27/02/2011</TD><TD style="TEXT-ALIGN: right">02/04/2011</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD> </TD><TD style="TEXT-ALIGN: right">16/01/2011</TD><TD style="TEXT-ALIGN: right">0.439367126</TD><TD> </TD><TD> </TD><TD> </TD><TD>Apr</TD><TD style="TEXT-ALIGN: right">03/04/2011</TD><TD style="TEXT-ALIGN: right">30/04/2011</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD> </TD><TD style="TEXT-ALIGN: right">23/01/2011</TD><TD style="TEXT-ALIGN: right">0.469906158</TD><TD> </TD><TD> </TD><TD> </TD><TD>May</TD><TD style="TEXT-ALIGN: right">01/05/2011</TD><TD style="TEXT-ALIGN: right">28/05/2011</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD>Feb</TD><TD style="TEXT-ALIGN: right">30/01/2011</TD><TD style="TEXT-ALIGN: right">0.315004064</TD><TD> </TD><TD> </TD><TD> </TD><TD>Jun</TD><TD style="TEXT-ALIGN: right">29/05/2011</TD><TD style="TEXT-ALIGN: right">02/07/2011</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD> </TD><TD style="TEXT-ALIGN: right">06/02/2011</TD><TD style="TEXT-ALIGN: right">0.516730731</TD><TD> </TD><TD> </TD><TD> </TD><TD>Jul</TD><TD style="TEXT-ALIGN: right">03/07/2011</TD><TD style="TEXT-ALIGN: right">30/07/2011</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD> </TD><TD style="TEXT-ALIGN: right">13/02/2011</TD><TD style="TEXT-ALIGN: right">0.824421833</TD><TD> </TD><TD> </TD><TD> </TD><TD>Aug</TD><TD style="TEXT-ALIGN: right">31/07/2011</TD><TD style="TEXT-ALIGN: right">27/08/2011</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD> </TD><TD style="TEXT-ALIGN: right">20/02/2011</TD><TD style="TEXT-ALIGN: right">0.696108022</TD><TD> </TD><TD> </TD><TD> </TD><TD>Sep</TD><TD style="TEXT-ALIGN: right">28/08/2011</TD><TD style="TEXT-ALIGN: right">01/10/2011</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD>Mar</TD><TD style="TEXT-ALIGN: right">27/02/2011</TD><TD style="TEXT-ALIGN: right">0.319618217</TD><TD> </TD><TD> </TD><TD> </TD><TD>Oct</TD><TD style="TEXT-ALIGN: right">02/10/2011</TD><TD style="TEXT-ALIGN: right">29/10/2011</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD> </TD><TD style="TEXT-ALIGN: right">06/03/2011</TD><TD style="TEXT-ALIGN: right">0.283810995</TD><TD> </TD><TD> </TD><TD> </TD><TD>Nov</TD><TD style="TEXT-ALIGN: right">30/10/2011</TD><TD style="TEXT-ALIGN: right">26/11/2011</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD> </TD><TD style="TEXT-ALIGN: right">13/03/2011</TD><TD style="TEXT-ALIGN: right">0.244958238</TD><TD> </TD><TD> </TD><TD> </TD><TD>Dec</TD><TD style="TEXT-ALIGN: right">27/11/2011</TD><TD style="TEXT-ALIGN: right">31/12/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>E2</TD><TD>=SUMPRODUCT(--($B$2:$B$55>=$H2),--($B$2:$B$55<=$I2),--($C$2:$C$55<>""))</TD></TR><TR><TD>F2</TD><TD>=SUMPRODUCT(--($B$2:$B$55>=$H2),--($B$2:$B$55<=$I2),$C$2:$C$55)</TD></TR><TR><TD>H2</TD><TD>=INDEX($B$2:$B$55,MATCH(G2,$A$2:$A$55,0))</TD></TR><TR><TD>I2</TD><TD>=H3-1</TD></TR><TR><TD>H13</TD><TD>=INDEX($B$2:$B$55,MATCH(G13,$A$2:$A$55,0))</TD></TR><TR><TD>I13</TD><TD>=MAX(B2:B55)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1
 
Upvote 0

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
You must log in or register to reply here.

Similar threads

R
  • Solved
How to independently sort multiple columns of this SEQUENCE output without effecting the positions of the columns and of the blanks?
2
Replies
15
Views
565
Rnkhch
R
A
  • Question Question
Conditional summing more complex than my brain
Replies
2
Views
258
Fluff
Fluff
R
  • Solved
" IF And" statement
Replies
2
Views
337
rmrossetti
R
S
  • Question Question
Excluding dates in summing up a range
Replies
3
Views
245
Fluff
Fluff
S
  • Question Question
Help with Conditional Formatting
Replies
2
Views
310
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,584
Messages
6,179,691
Members
452,938
Latest member
babeneker

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