Array Forumla Help

[Paul75]

Hi,
I'm a reasonably basic user so looking for some help with a formula please.

I have 2 columns. Column 'A' has dates, column 'B' has numbers. What I'm trying to achieve is that when the user enters 2011 on a different worksheet then it will sum all the numbers in column 'B' where the corresponding date has the year 2011. This I have achieved with the following formula;
{=IF(ISBLANK($A1),"",SUM(IF(YEAR(FlightDates)=$A1,Hours_Total_Flight)))} where 'FlightDates' is column A and Hours_Total_Flight is column B.

What I can't seem to do is to sum column B for a certain year and a certain month ie, the user has selected 2011 and 4. I would want all the flight hours from April 2011 to be summed.

Not sure if that makes too much sense. Hopefully does.
Looking forward to any feedback or help.

Regards,
Paul.

 

[barry houdini]

You could use SUMPRODUCT and avoid "array entering", e.g. if month number is entered in B1 try

=IF(COUNT($A1,$B1)=2,SUMPRODUCT((YEAR(FlightDates)=$A1)*(MONTH(FlightDates)=$B1),Hours_Total_Flight),"")

 

[Aladin Akyurek]

Hi,
I'm a reasonably basic user so looking for some help with a formula please.

I have 2 columns. Column 'A' has dates, column 'B' has numbers. What I'm trying to achieve is that when the user enters 2011 on a different worksheet then it will sum all the numbers in column 'B' where the corresponding date has the year 2011. This I have achieved with the following formula;
{=IF(ISBLANK($A1),"",SUM(IF(YEAR(FlightDates)=$A1,Hours_Total_Flight)))} where 'FlightDates' is column A and Hours_Total_Flight is column B.

What I can't seem to do is to sum column B for a certain year and a certain month ie, the user has selected 2011 and 4. I would want all the flight hours from April 2011 to be summed.

Not sure if that makes too much sense. Hopefully does.
Looking forward to any feedback or help.

Regards,
Paul.

Control+shift+enter, not just enter:

=IF(OR($A1="",$B1=""),"",SUM(IF(YEAR(FlightDates)=$A1,IF(MONTH(FlightDates)=$B1,Hours_Total_Flight))),"")

where B1 houses a month number of interest.

 

[Paul75]

Thanks heaps Barry and Aladin.

Work perfectly thanks Aladin, I was missing an IF.

Your help is very much appreciated.

 

[Paul75]

Sorry one more question Aladin.

That formula works if I enter a month and a year, it will sum all the hours in say Apr 2011. How do I make it that if the month field is left blank but 2011 is still entered all the hours from 2011 are still summed.
At the moment if I leave the month field empty but still have a year it returns 0.0.

Regards.

 

[pgc01]

Hi Paul

Try:

=IF($A1="","",SUM(IF(YEAR(FlightDates)=$A1,IF((MONTH(FlightDates)=$B1)+($B1=""),Hours_Total_Flight))))

 

[Aladin Akyurek]

Hi Paul

Try:

=IF($A1="","",SUM(IF(YEAR(FlightDates)=$A1,IF((MONTH(FlightDates)=$B1)+($B1=""),Hours_Total_Flight))))

Perhaps the initial a bit modified...

=IF($A1&$B1="","",SUM(IF(YEAR(FlightDates)=$A1,IF((MONTH(FlightDates)=$B1)+($B1=""),Hours_Total_Flight))))