Excel SUMIF Function with Multiple Criteria for One Range (not SUMIFS)

Question

UPDATE 2

Here is my formula and the resulting error. There are "#DIV/0" results in column J in some of the March data.

END OF UPDATE 2

---

UPDATE

So as you can, I am trying to sum up column J for each month (those totals will go in column R-W). So I need to the SUMIF function to look at data that begins with a certain date, which I was able to figure out, but also to not include anything in the sum that is an error.

END OF UPDATE

I've looked around for a bit for an answer to this question but what I am finding is people are using SUMIF when they are looking at two different ranges; not what I'm trying to do.

I am trying to do a SUMIFS that looks at ONE range and has multiple criteria.

For example, I have 262,025 rows in this file. I am trying to write a function that does a summation only if the date begins with 2016/03. The problem is, the data I am actually summing contains some "#DIV/0" errors in it.

This is what I was thinking:

SUMIF(E6:E262025, AND("2016/03*", <>"#DIV/0", J6:J262025)

This does not work though.

Basically, I want to sum up column J only if the month is March and I want to exclude any of the values in column J that are "#DIV/0". If this is indeed a SUMIFS function then my apologies. I looked at the syntax for that function and I didn't think that was what I needed. Looking forward to some feedback.

Answer 1

A simple SUMIFS will do:

=SUMIFS(J:J,E:E,"<=3/31/2016",E:E,">=3/1/2016")

It will ignore the errors.

---

EDIT

Now that you posted a picture it is obvious that your dates are not actually dates but text that look like dates. Then you can use this simple SUMIF:

=SUMIF(E:E,"2016/3*",J:J)

Again it will ignore the error.

---

Edit #2

If the errors are in the value column then we will need to use an array formula that ignores them:

=SUM(IF(ISNUMBER(SEARCH("2016/03",E6:E262025))*(ISNUMBER(J6:J262025)),J6:J262025))

Being an array formula it must be confirmed with Ctrl-Shift-Enter upon exiting Edit Mode instead of Enter. If done correctly then Excel will put {} around the formula.