Formula to show monthly count

Irene Plant

Hi, I am trying to figure out a formula that tells me how many entries have been entered for a particular month. I have a date column and an tonnage column so if I have 10tonne of material made on 1st February i would enter in the date column 01/02/16 and next to it in the tonnage column I would enter 10. I also have a total tonnage for February column, total tonnage for March column and so on for the whole year. I am trying to figure out a way to get the amount of tonnage entered for february listed in my total tonnage for february column as the amount of entries. For example if I had 6 entries under the date column listed as february I want the total tonnage for February to say 6. Any ideas on formulas for this one at all?

Many thanks, Irene

JamesR

There are two ways to tackle tis:

1  Create a Column that extrapulates the month from the date - = Month(Date).  This will give you a number between 1 and 12.

In the Feb column usr a Sumif to add all the values fron the tonnage column if the month colum =2

 

2 Redesign the sheet ito Hierarchys by month and use the =Sun(Children() to give you the totals for each month.

Irene Plant

Thanks for your help James, your first option is the one I was thinking of using but don't know how to write the formulas to get the month to be number two if the date is 01/02/16 for example, any ideas?

Many thanks.

Irene Plant

Sorry James, just found the answer! Thank you for your help I've set it all up now

Irene Plant

SORRY JAMES!! The formula I did for 'In the Feb column usr a Sumif to add all the values fron the tonnage column if the month colum =2' hasn't worked out......any suggestions?

 

JamesR

• Syntax: SUMIF(criteria_range, criteria_value, sum_range)

Criteria_range = Column that contains the Month Number (Month:Month)

Criteria_value = 2

sum_range = column that has the numbers to add up(Tonnage:Tonnage)

=SUMIF(Month:Month,2,Tonnage:Tonnage)