If statement and Count

r!chg · Jun 1, 2011

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?

Andrew Poulsom · Jun 1, 2011

Sorry, it's not possible for a count of the cells in rows 61 to 95 return more than 35.

WinteE · Jun 1, 2011

Hi,

Your question is not clear enough to give you an answer.

On sheet Data_2011_Day is there data besides the data in column F ? For example a column with dates ?

r!chg · Jun 1, 2011

Hi,

Your question is not clear enough to give you an answer.

On sheet Data_2011_Day is there data besides the data in column F ? For example a column with dates ?

Yes there is. The Date column is in Column B of the sheet.

Sorry, it's not possible for a count of the cells in rows 61 to 95 return more than 35.

I agree is should only return 35. But in order for it to return 35 I have to skip row 61. I was hoping for it to to be consecutive. Hence why I underlined the ranges.

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

The reason for this approach is I'm trying to create a month to date table.
If the end user selects "Jan" the formula will return the sum of data for the days for Jan. In this example it will be 28 (Companies way of counting number of days in January). By using Count, in the current month (June) it should only count for the days of June in which data is given. So Only one day of data. Tomorrow 2 Days of Data. So it will give me a month to date snap shot of the data.

I hope this makes sense. Im open to other suggestions for making a month to date table.

I will attached an example of the data set

jasonb75 · Jun 1, 2011

Try

=SUMPRODUCT(--(TEXT(Data_2011_Day!B5:B96,"mmm")='MTD (2)'!$C$3),--ISNUMBER(Data_2011_Day!F5:F96))

r!chg · Jun 1, 2011

Hi Jasonb75.

Correct me if I'm wrong but that formula does works for standard excel dates.

But the company has its own formats for days in the month. Jan, Feb, Apr and May have 28 Days.
Mar and June have 35 days.

jasonb75 · Jun 1, 2011

Does that follow for the remaining 6 months?

Jul - Aug - Oct -Nov 28 days, Sept & Dec 35 days?

That totals 364 days in a year, what about the last day (or 2 days in leap years)?

r!chg · Jun 1, 2011

I'm Trying to copy an image over but with not much luck.
Forum User fail HA HA HA!!

Column A Blank with a reference to when the month starts.
Column B Date. Starting from 01/01/2011 (in that format)
Column C Passengers number of passengers for corresponding day in column B
Column D Crates number of crates in use.
Column E number of bags in use.

Jan starts on the 2nd. Feb starts on the 30th of Jan.

Hope this helps

C:%5CDocuments%20and%20Settings%5CRgilfillan%5CMy%20Documents%5CMy%20Picture%5CMTD%20example%20sheet

jasonb75 · Jun 1, 2011

Column A Blank with a reference to when the month starts.

That could be useful, how is the reference entered?

Which version of excel are you using?

r!chg · Jun 1, 2011

Hi Jasonb75

Does that follow for the remaining 6 months?

Jul - Aug - Oct -Nov 28 days, Sept & Dec 35 days?

That totals 364 days in a year, what about the last day (or 2 days in leap years)?

This is the response I got from the finance team.

 The year is always broken into the 4 4 5 week structure, the year end dates move which is where the day is lost. Last year end was 1<sup>st</sup> Jan, this year end is 31<sup>st</sup> of Dec.

If statement and Count

r!chg

New Member

Excel Facts

Andrew Poulsom

MrExcel MVP

WinteE

Well-known Member

r!chg

New Member

jasonb75

Well-known Member

r!chg

New Member

jasonb75

Well-known Member

r!chg

New Member

jasonb75

Well-known Member

r!chg

New Member

Similar threads

Forum statistics

Share this page

If statement and Count

New Member

Excel Facts

MrExcel MVP

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock