Multiple conditions based on end of month

[Binghamcornwall]

I have a spreadsheet 'Log' that contains an eomonth formula '=EOMONTH(A4,0)', formatted to custom mmm-yy On a separate sheet I am trying to collate stats on the number of times two conditions are met, one of which is based on the end of month mentioned above.

The formula I am using is '{=SUM((Log!C4:C134="New")*(Log!X4:X134="May-10"))}, using control, shift and enter to enter the formula to establish {}.

The formula is consistently returning zero, when I know it should have a total. I am assuming the problem lies with the eomonth format. Can anyone help?

The solution may be simple, but I appear to be missing it. Many thanks.

 

[xld]

The format is irrelevant. Try

=SUM((Log!C4:C134="New")*(Log!X4:X134=--"31-May-10"))

 

[AlphaFrog]

"May-10" is a text string and not an actual date.

Try something like this...
{=SUM((Log!C4:C134="New")*(Log!X4:X134=DATE(2010,5,31)))}

Or this
{=SUM((Log!C4:C134="New")*(Log!X4:X134=DATEVALUE("May 31, 2010")))}

 

[njimack]

The formatting doesn't change the fact that your EOMONTH formula is actually returning a date. Try the following (non-array) approach:

=SUMPRODUCT(--(Log!C4:C134="New"),--(MONTH(Log!X4:X134)=MONTH(A4)),--(YEAR(Log!X4:X134)=YEAR(A4))

 

[Binghamcornwall]

Many thanks.

The formula that worked was =SUM((Log!C4:C134="New")*(Log!X4:X134=DATEVALUE("31 May 2010"))) entered as an array formula.

I missed the fact that May-10 was a text string, another lesson learnt!

Again many thanks for the help, it is much appreciated.