Don't count blank cells in sum array

[mnunan]

Hi

Can anybody help with correcting this sum array please?

I am trying to count the records in column L where the date in column D falls between the dates specified in cells B2 and C2. This is working just fine, except some of the cells in column D are blank and I don't want these counted but no matter what I have tried it keeps counting them. (content of column D is text).

Thanks for any help

=SUM(('RAW DATA'!$D$2:$D$50000>=B2)*('RAW DATA'!$D$2:$D$50000<=C2),('RAW DATA'!$L$2:$L$50000),0)

 

[T. Valko]

Hi

Can anybody help with correcting this sum array please?

I am trying to count the records in column L where the date in column D falls between the dates specified in cells B2 and C2. This is working just fine, except some of the cells in column D are blank and I don't want these counted but no matter what I have tried it keeps counting them. (content of column D is text).

Thanks for any help

=SUM(('RAW DATA'!$D$2:$D$50000>=B2)*('RAW DATA'!$D$2:$D$50000<=C2),('RAW DATA'!$L$2:$L$50000),0)

Ok, you first say column D are dates then you say column D is TEXT.

Which is it?

If column D is true Excel dates then this test will fail for truly empty cells:

'RAW DATA'!$D$2:$D$50000>=B2

So, are you sure the cells are truly empty?

 

[mnunan]

Hi

Thanks for answering and my apologies, I meant content of column L is text

 

[T. Valko]

Hi

Thanks for answering and my apologies, I meant content of column L is text

OK, my point still applies...

If column D is true Excel dates then this test will fail for truly empty cells:

'RAW DATA'!$D$2:$D$50000>=B2

So, are you sure the cells are truly empty?

Also, if column L contains TEXT then why are you referencing it in the formula:

=SUM(('RAW DATA'!$D$2:$D$50000>=B2)*('RAW DATA'!$D$2:$D$50000<=C2),('RAW DATA'!$L$2:$L$50000),0)

 

[Aladin Akyurek]

Hi

Can anybody help with correcting this sum array please?

I am trying to count the records in column L where the date in column D falls between the dates specified in cells B2 and C2. This is working just fine, except some of the cells in column D are blank and I don't want these counted but no matter what I have tried it keeps counting them. (content of column D is text).

Thanks for any help

=SUM(('RAW DATA'!$D$2:$D$50000>=B2)*('RAW DATA'!$D$2:$D$50000<=C2),('RAW DATA'!$L$2:$L$50000),0)

Control+shift+enter, not just enter:

=SUM(
    IF('RAW DATA'!$D$2:$D$50000>=B2,
    IF('RAW DATA'!$D$2:$D$50000<=C2,
      1-('RAW DATA'!$L$2:$L$50000=""))))

 

[T. Valko]

Hi

Can anybody help with correcting this sum array please?

I am trying to count the records in column L where the date in column D falls between the dates specified in cells B2 and C2. This is working just fine, except some of the cells in column D are blank and I don't want these counted but no matter what I have tried it keeps counting them. (content of column D is text).

Thanks for any help

=SUM(('RAW DATA'!$D$2:$D$50000>=B2)*('RAW DATA'!$D$2:$D$50000<=C2),('RAW DATA'!$L$2:$L$50000),0)

It would also help if we knew what version of Excel you're using.

 

[mnunan]

OK, my point still applies...



Also, if column L contains TEXT then why are you referencing it in the formula:

=SUM(('RAW DATA'!$D$2:$D$50000>=B2)*('RAW DATA'!$D$2:$D$50000<=C2),('RAW DATA'!$L$2:$L$50000),0)

Because I only want to count the dates in column D where the corresponding row in column L is not blank, hope that makes sense. Am using 2007

 

[mnunan]

Control+shift+enter, not just enter:

=SUM(
    IF('RAW DATA'!$D$2:$D$50000>=B2,
    IF('RAW DATA'!$D$2:$D$50000<=C2,
      1-('RAW DATA'!$L$2:$L$50000=""))))

Yes this is set as an array but when you copy the formula from excel it doesn't insert the curly brackets

 

[T. Valko]

Hi

Can anybody help with correcting this sum array please?

I am trying to count the records in column L where the date in column D falls between the dates specified in cells B2 and C2. This is working just fine, except some of the cells in column D are blank and I don't want these counted but no matter what I have tried it keeps counting them. (content of column D is text).

Thanks for any help

=SUM(('RAW DATA'!$D$2:$D$50000>=B2)*('RAW DATA'!$D$2:$D$50000<=C2),('RAW DATA'!$L$2:$L$50000),0)

Ok, I think I just realized what you were trying to say.

Column L has the empy cells, not column D.

You can modify your current formula to:

=SUM(('RAW DATA'!$D$2:$D$50000>=B2)*('RAW DATA'!$D$2:$D$50000<=C2)*('RAW DATA'!$L$2:$L$50000<>""))

Or, you can use this version:

=SUM(IF('RAW DATA'!$D$2:$D$50000>=B2,IF('RAW DATA'!$D$2:$D$50000<=C2,IF('RAW DATA'!$L$2:$L$50000<>"",1))))

Or, you can use this normally entered version which will work in any version of Excel:

=SUMPRODUCT--('RAW DATA'!$D$2:$D$50000>=B2),--('RAW DATA'!$D$2:$D$50000<=C2),--('RAW DATA'!$L$2:$L$50000<>""))

Or, if you're using Excel 2007 or later:

=COUNTIFS('RAW DATA'!D2:D50000,">="&B2,'RAW DATA'!D2:D50000,"<="&C2,'RAW DATA'!L2:L50000,"<>")

If you're using Excel 2007 or later then the COUNTIFS version is the best option.

 

[mnunan]

Ok, I think I just realized what you were trying to say.


Or, if you're using Excel 2007 or later:

=COUNTIFS('RAW DATA'!D2:D50000,">="&B2,'RAW DATA'!D2:D50000,"<="&C2,'RAW DATA'!L2:L50000,"<>")

If you're using Excel 2007 or later then the COUNTIFS version is the best option.

I used this version as advised and it worked instantly, thanks so much for your patience, have a good day