Sumif certain criteria

[doylzer]

Hi All,

Hope you can help me solve my excel problem.

I have attached the excel example of what I am about to refer too!

Yellow cells refer to cell that are lists and I would like to change. So the formula needs to link back to these and be flexible as possible

I am trying to find the sum of numbers that are between the two dates (C20:D20) and that match the criteria in A20:B20, however I can only sum the criteria that have no blank cell which match that criteria.

In my example, I have highlighted the cells that would be summed in blue but I have also highlighted cells in red that can’t be added to the total due to the blank cells at H11:K11 even though it matches the criteria

I need to be able to change the dates. So the formula needs to be flexible to notice if blanks cells are present in the range and therefore not added.

I hope I made myself as clear as possible.

[Ron Coderre]

Using your sample workbook
If you want to sum the values where:
Trade Category equals Food
and
Street equals Barry
and
Column heading is between Jan-13 and Oct-13

Try this formula:

Please Login or Register  to view this content.

For your data, that formula returns: 2,740,212

Is that something you can work with?

[doylzer]

thanks Ron.

however this is not exactly what im looking for

I need to exclude the entire row(F11:O11) of light red cells as they have blank cells (dark red).

So the total should read 2,346,712

[newdoverman]

I added a helper column that determines if there are blanks in the rows between the columns represented by the dates in C20 and D20. The helper column is column Q
Enter in Q2 and fill down:

Please Login or Register  to view this content.

Enter this modified formula in D20

Please Login or Register  to view this content.

[doylzer]

Thanks everyone. that last post worked perfectly. Much appreciated.

[newdoverman]

You're welcome. Thank you for the feedback and good luck with the project.