Displaying Specific Rows and Summing Values in a Column with Invalid Data

Hello, I have 2 questions about Excel 2003:

1) I want to extract (display) all rows where columns G-M have the value of "No".

2) How do you sum a column of values where you may have invalid data in those cells like an invalid date value of 41235? If any invalid data is in the cell, I want the sum function to see it as a 0.

Thanks so much,
Terrie

First, thank you for such a quick response.

In reference to my first question, I need a total count of all rows that have "No" in columns G:M. So, could you please explain how placing that formula in the cell holding the count will give me a correct result? Thanks for your patience. I'm learning...

In reference to your second response, what does "&20000" mean? Right now, the formula I'm using (which of course adds the cell with the date value) is =SUMIF(rawdata1!EA3:EA132, ">0").


Thanks again,
Terrie

First question: I have 130 rows that Excel has to search through to see if G:M have all "No"s.

Then to exclude the dates after 1985 you can use SUMIFS instead.

=SUMIFS(rawdata1!EA3:EA132, ">"&0,rawdata1!EA3:EA132,"<"&30000)

This translates to sum the cells which are greater than zero AND less than 30000.

The ampersand "&" is used for concatenation. It tells Excel to evaluate what comes before and after as two separate expressions. "<0" will work in many SUMIF scenarios, but it can create issues down the road. Breaking the operator "<" out from the zero tells Excel that it is an operator and not that you're literally searching for cells with "<0" in them. It may work for you now, but understanding how the function works can prevent issues down the road in other situations.

I entered your SUMIFS function and Excel did not like it. It said, I had entered too few arguments.

Also, did you have a response to my first question's reply?

Thanks

sorry. please ignore the last question. but I did have a problem with the SUMIFS.

Oops. =SUMIFS(rawdata1!EA3:EA132,rawdata1!EA3:EA132, ">"&0,rawdata1!EA3:EA132,"<"&30000)

Missed the sumrange.

SUMIFS(sum range, criteria range1, criteria1, etc)

The SUMPRODUCT worked like a champ! Thanks! Trying the SUMIFS again now.

The SUMIFS worked too. Again, thank you.

Hurray! Glad I could help out.

What does this mean?
=SUMIF(E19:E33,"5",D19:D33)+SUMIF(E19:E33,"",D19:D33)

To me, it's saying if for each cell from 19 through 33 of column E, there's a 5 there, then use it in the sum. But what's happening with the + symbol and what appears to be adding a blank string. Does the plus symbol represent concatenation?

Thanks.

Nopes!

SUMIF(criteria range, criteria, [sum range])

If E19:E33 contains a 5, add up the same row in column D.

If no secondary range is specified, it will add up the first range. In your case a 2nd range was specified so it's adding up in E instead.

daffodil11, I figured it out after posting the question, but I sure appreciate your response. Good explanation. Thank you.