Ignore blanks using =SUMIF/COUNTIF

[Casper9T9]

I've come across a problem using the =SUMIF/COUNTIF function in excel. It incorrectly calculates blank cells as zeros.

=SUMIF(D4:D54,"Solar",T4:T54)/COUNTIF(D4:D54,"Solar")

The correct output should be 100%, but shows as 66.67% because of the blank cells in the column. How do I correct this?

[daddylonglegs]

Try

=SUMIF(D4:D54,"Solar",T4:T54)/SUMPRODUCT((D4:D54,"Solar")*(T4:T54>0))

[Casper9T9]

That formula results in an error. It does not appear to recognize the last array>0. I've attached the file for reference.

[daddylonglegs]

Sorry, my mistake, I had a comma where there should be a =

Try this version

=SUMIF(D4:D54,"Solar",T4:T54)/SUMPRODUCT((D4:D54="Solar")*(T4:T54>0))

or to avoid #DIV/0! error when the SUMPRODUCT evaluates to zero

=SUMIF(D4:D54,"Solar",T4:T54)/MAX(1,SUMPRODUCT((D4:D54="Solar")*(T4:T54>0)))

[mubashir aziz]

Try this Array formula and don't forget to press Ctrl+Shift+Enter to make it array ..... when you will press these keys then { } will come around the formula. Never enter {} manually

=SUM(IF(D4:D54="Solar",H4:H54))/COUNT(IF(IF(D4:D54="Solar",1)*IF(H4:H54<>"",H4:H54)>0,H4:H54))

[Casper9T9]

Thanks for the help. Both solutions work.

I have one question though. If the cell value is 0%, then it will not calculate that value in the average. Is there a way to avoid blanks, but include 0? If anything, I can put a note on top to input 0.01% instead of 0.00%.

[mubashir aziz]

I don't under stand you as you said [

I]if the If the cell value is 0%, then it will not calculate that value in the average. Is there a way to avoid blanks[/I]

I think you mean to say that you want to include Blank Cells ..... then below formula will work well ....

Suppose you have data from A2:A9 then use this formula to calculate the average ...

=SUM(A2:A9)/(COUNTA(A2:A9)+COUNTBLANK(A2:A9))

[JBeaucaire]

My non-array solution in H57:

=SUMIF($D$4:$D$54,"Solar",H4:H54)/(SUMPRODUCT(--($D$4:$D$54="Solar"),--(H4:H54<>""),H4:H54)+COUNTIF(H4:H54,0))

This will ignore blank/null, but will count actual zero values.

[stanley]

I dont know if I understand, but you want know calculate cells with numbers and no calculate blank cells....if you know a little VBA try this:

1. Alt+F11 - for VBA
2. Insert Module
3. Write:

Function Sum_Visible_Cells(Cells_To_Sum As Object)
Application.Volatile
For Each cell In Cells_To_Sum
If cell.Rows.Hidden = False Then
If cell.Columns.Hidden = False Then
Total = Total + cell.Value
End If
End If
Next
Sum_Visible_Cells = Total
End Function

4. Exit VBA
5. write to cell: =Sum_visible_cells(A1:A20)
6.Done

[JBeaucaire]

Hi Stanley, welcome to the forum.

I'm not sure about that function, but you do need to follow the forum rules regarding code. Take a look at this post and you'll see what I mean:
http://www.excelforum.com/excel-prog...orkbook.html#3

Please go back to post #9 in this thread, click EDIT > GO ADVANCED, highlight all that code, then click the # icon to wrap it in [ code ] [ /code ] tags.

Thanks!

[Casper9T9]

My non-array solution in H57:

=SUMIF($D$4:$D$54,"Solar",H4:H54)/(SUMPRODUCT(--($D$4:$D$54="Solar"),--(H4:H54<>""),H4:H54)+COUNTIF(H4:H54,0))

This will ignore blank/null, but will count actual zero values.

This is exactly what I need. Thank you.