CountIF and SUMIF

Question

I have a table with a column I need to sum the values for.  However, I only want to include that value in the row if another value in that row has a date of greater than the value held in cell B1 which is a cell in which I store the start date.  The date fields are in column H and I'm summing all of the values in column O if the value of column H for that row is greater than B1. 

I also need to do a count on another column if the value of H is > B1 but I think I should be able to reuse the answer provided for the SUM issue.

Answer 1

Hi there,

You can use something like this for the sum...

=SUMIF(H2:H10,">="&B1,O2:O10)

This assumes the data range goes from row 2 to 10, header row in row 1.

If you're using an actual Table, it could look like this...

=SUMIF(Table1[Date Fields],">="&B1,Table1[Sum Values])

Where you would need to change the Table name and column headers to match.

The count formula would look like this...

=COUNTIF(H2:H10,">"&B1)

or

=COUNTIF(Table1[Date Fields],">"&B1)

HTH

Answer 2

Hi Zach,

I modified the sample you provided and came up with this:

=CountIF(Table_owssvr_1[Start Date],">="&B1,Table_owssvr_1[Change Owner], "DEV")

But it doesn't work... Basically, I need to know the number of "DEV" values in the Change Owner column if the DEV value's Start Date value is >= The value in B1.

What I listed about represents the real table name and column names.

Answer 3

You need COUNTIFS for multiple conditions rather than COUNTIF.

Answer 4

Yup, just like Rory says, you need COUNTIFS, where you can specify multiple conditions. Just type out the formula and when you enter your first paren ( you will see the syntax. You will enter data in pairs, starting with the range to look at, then the criteria. These are all and conditions. Any or conditions will need to be handled differently. The syntax you have looks right, should just have to add the "S".

Answer 5

Thanks guys, adding the S did work.  However, another problem has popped up... For one of my conditions I'm getting a value when I shouldn't.

=COUNT(Table_owssvr_1[Start Date],">="&B2,Table_owssvr_1['# of ABENDS])

I'm getting a value of 2 right now and it should be zero.   Also, if I enter values into the field and don't have the right date in the date field meaning a date that is not valid for the condition the item in the # of ABENDS column is still being counted.