SUMIF blank cells containing formula

[Charmwah]

Hi all

Probably a very simple answer, but I have a series of SUMIF cells which all work perfectly, until they reach a blank cell which contains a formula. I can't find the right syntax for the SUMIF to work, any ideas? The code at present is:

=SUMIF($AI$2:$AI$328,"",$AD$2:$AD$328)

Where column AI is the range to check, and column AD is the number to count if the criterion is met.

Kind regards

Dave

 

[Joe4]

Zeroes or empty cells should not hurt SUMIF equations (adding zero to any value does not change it).
So it shouldn't be necessary to filter them out.

If you really want to COUNT the cells, and not SUM them, you would use COUNTIF, not SUMIF.

If you need help getting it to work, please clarify what exactly is in these cells that you are trying to Count or Sum, and that the formula that returns the values in those cells looks like.

 

[Charmwah]

Hi Joe4

The cells are blank, not zeros. The formula thats inside each cell is an IF statement that analyses some adjacent columns, returning a priority value when two columns are populated:

=IF(AH2="",AF2,AH2)

Thanks

Zeroes or empty cells should not hurt SUMIF equations (adding zero to any value does not change it).
So it shouldn't be necessary to filter them out.

If you really want to COUNT the cells, and not SUM them, you would use COUNTIF, not SUMIF.

If you neeH263="",AF263,AH263)d help getting it to work, please clarify what exactly is in these cells that you are trying to Count or Sum, and that the formula that returns the values in those cells looks like.

 

[Joe4]

The cells are blank, not zeros.

SUM and SUMIFS ignore blanks and text by default, so you shouldn't have to worry about them.

Unless of course you are really trying to do a COUNT and not a SUM, as I mentioned in my first response. You haven't clarified that for us.

What exactly do these values being returned by your formula look like? Are they numbers, text, etc?

It might really clarify things if you post a small sample, along with your expected results. Then we will be able to see what you are really after.

 

[Charmwah]

Hi Joe4

I'm afraid I can't post the file as it contains private data. However, I've just worked out the problem by your response. If the SUMIF's ignore the formula in the cell then I figured it must be an error in the formatting of the data it was totaling.

Thanks for your time and effort, really appreciate it.

SUM and SUMIFS ignore blanks and text by default, so you shouldn't have to worry about them.

Unless of course you are really trying to do a COUNT and not a SUM, as I mentioned in my first response. You haven't clarified that for us.

What exactly do these values being returned by your formula look like? Are they numbers, text, etc?

It might really clarify things if you post a small sample, along with your expected results. Then we will be able to see what you are really after.

 

[Joe4]

Excellent!

Glad you got it figured out.