Using If cell not empty as criteria within a SUMIFS statement

I'm using the following SUMIFS statement to Sum column N, based on some criteria:
Criteria 1 - Field in column H = Done
Criteria 2 - Date in column L >= date specified
Criteria 3 - Date in column L <= date specified
Criteria 4 - ????

=SUMIFS('RAW DATA'!$N$2:$N$999, 'RAW DATA'!$H$2:H$999, "Done", 'RAW DATA'!$L$2:L$999, ">=" &A14, 'RAW DATA'!L$2:L$999,"<=" &B14, 'RAW DATA'!$M$2:M$999, "??????????",)

For the final criteria, I'm trying to make it that the cell in column M is NOT EMPTY (it has to be populated with something).

Could someone please help me with the correct syntax for this?

Thanks

Try

=SUMIFS('RAW DATA'!$N$2:$N$999, 'RAW DATA'!$H$2:H$999, "Done", 'RAW DATA'!$L$2:L$999, ">=" &A14, 'RAW DATA'!L$2:L$999,"<=" &B14, 'RAW DATA'!$M$2:M$999, "<>",)

Thanks John. That doesn't seem to work - it is still counting data from rows where the field in column M is empty.

Try

=SUMIFS('Raw data'!$N$2:$N$999, 'Raw data'!$H$2:H999, "Done", 'Raw data'!$L$2:L$999, ">=" &A14, 'Raw data'!L$2:L$999,"<=" &B14, 'Raw data'!$M$2:M$999, ">0")

I am guessing M is not blank but contains a formula ????

I couldn't seem to get that to work either

I've attached my Excel, keeping only the required data. There isn't currently a formula in M - just text.

The way I'm validating it's not working is by filtering out the blanks in the RAW DATA tab and summing the values in N. The SUM is much less than the SUM I'm getting in Sheet1. Also when I drag the new formula down I don't see any change in the numbers.

Thanks again for the help.

How about

Formula:

Please Login or Register  to view this content.

I looked at the results in D16 which has a value of 29

I filtered the data on January : row 275 has "CAA" in column M and value in N of 2. I deleted the CAA and the result in D16 was 27 i.e it had ignored the the blank M. Similar for row 361: removed "DBW"/ N =20 and result in D16=9 (I had reinstated the "CAA".

So I don't know what you are doing but the formula works as required.

=SUMIFS('RAW DATA'!$N$2:$N$999, 'RAW DATA'!$H$2:H$999, "Done", 'RAW DATA'!$L$2:L$999, ">=" &A16, 'RAW DATA'!L$2:L$999,"<=" &B16, 'RAW DATA'!$M$2:M$999, "<>")

@JohnTopley that formula totals to 485, but if you look at the filtered sheet it should total to 419
It's ignoring the column M criteria, also as the dates on the Raw Data sheet include time some of them are not being counted.

Yes, this was the same reason I thought it wasn't working and not taking into account the M criteria. On Sheet1, with the formula, it totals 485, but when I filter with the same criteria on RAW DATA tab it totals 419 so something doesn't add up - it still seems to be counting some of the rows where M is empty.

Thanks again

Did you try the formula I suggested?

Sorry, i completely missed your suggestion - that does work, thank you!

I'd love to know why the '&B2+1' ? I see that before adding this, the numbers don't add up but I don't understand why it requires that?

What does the "?*" syntax mean?

Thanks for the help!

The ?* looks for any text value that is a minimum of 1 character long, so it ignores the null strings that are in col M.
The dates in col L contains date & time so on row 23 you have an end date of 7th Mar & there are 5 rows that match that date, but if you swap the format to General you will those rows show
Excel 2016 (Windows) 32 bit

	L
125	44262.93
132	44262.93
208	44262.93
226	44262.93
262	44262.93

Sheet: RAW DATA

Which means they are all greater than the date alone (44262), so you need to add 1 to the date & use < rather than <=

The problem is DATA: there are cells in M which appear blank but actual contain "rogue" data,

If you filter on M=blank, then Clear contents, the total is 396.

I tested a specific date range where the original result given was 13 but should have been 11. Guess what there was a "blank" M with N=2.

I cleared the M and result was 11.

That still does not take into account the fact that the cells contain date and time.

Removing the time from column L makes no difference to final result.

It does for me.

With the formula I suggested 419, with your formula after cleaning col M 396.
The only row affected is row 23 where I get 64 rather than 41

I have just rerun and I now get 419 with 64 in row 23: not sure why I had the other results but now we agree so hopefully all is well!

so hopefully all is well!

Agreed.

Thanks very much for the help - I think I understand it now.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

Sorry, just one more question - how do I find and clear the rogue/hidden data you mention? Do I just filter by the blank cells and hit delete?

Filter and "Clear Contents" should suffice.