Sumif problem

[Gil D.]

Hello,

My problem is that I don't know weather the sumif returns zero because
there is no value is the choosen lines or the total sum of choosen
lines values is zero.

For example:

Case 1:
group value
A 0
A

sumif which sums values for group A lines will return: 0

Case 2:
group value
A
A

sumif which sum values for group A lines will return: 0

How can I check wheather all choosen lines are empty ?

Thank you for yor help
Gil D.

 

[jasonsweeney]

Try:
[If your using formulas, put this under the two cells you want to
add]:
=If (or(isblank([cell1]) = TRUE, isblank([cell2])), "One is Blank",
sum([your cell range]))

[If VBA]:
if Sheet[x].range("[a]").value = "" or Sheet[x].range("b]").value = ""
then
msgbox("One is Blank")
Else
Sheet[x].range("[c]").value = Sheet[x].range("[a]").value +
sheet[x].range("").value
end if

 

[Tom Ogilvy]

Use countif to check.

 

[Gil D.]

Hello,

Thank you for your help.

My problem is that I have max 15 rows that I should check.

How can I use isBlank for a range ?

Thank you
Gil D.

 

[Tom Ogilvy]

Sorry, misread your question

=Sumproduct(--(A1:A10="A"),--(B1:B10)="")

will give you the number of blanks.

 

[Gil D.]

Hello,

I don't know how to use countif to check it.
I need to check which rows in the group-column = "A" (for example)
and then if all the rows in the value-column are empty.

How can I do this ?

Case 1 (Some group A rows are not empty):

group,value
A,0
A,0
B,3
A,0
B,8
C,5
A
..
..
..


Case 2 (All group A rows are empty):

group,value
A
A
B,3
A
B,8
C,5
A
..
..
..


Thank you
Gil D.

 

[jasonsweeney]

Use a Macro:
-- Assumes your data is in Column "B"
-- Assumes your data starts in row 1
-- Places sum results in Cell B16
-- Places blank results in Cell C16
_______________

sub check_for_blanks()
' Set your results to zero
Sheet1.cells(16,2).value = 0
Sheet1.cells(16,3).value = 0
' loop through the cells
For i = 1 to 15
If Sheet1.cells(i,2).value = "" then
Sheet1.cells(i,3).value = "Is Blank"
Sheet1.cells(16,3).value = Sheet1.cells(16,3).value + 1
Else
Sheet1.cells(16,2).value = Sheet1.cells(16,2).value +
sheet1.cells(i,2).value
End if
Next
end sub

 

[Tom Ogilvy]

=if(countif(A1:A10,"A")-Sumproduct(--(A1:A10="A"),--(B1:B10)="")=0,"All
blank",if(Sumproduct(--(A1:A10="A"),--(B1:B10)="")>0,"some blank","none
blank"))

 

[Gil D.]

Hello,

Thank you for your help.

I am trying to do what you have suggested but I think that Sumproduct
returns wrong values.

row, group (columnA), value(columnB)
1,A,6
2,A,Null
3,B,6
4,A,Null
5,A,Null
6,C,9
7,C,7
8,C,5
9,C,5
10,D,3

=SUMPRODUCT(--(A1:A10="A"),--(B1:B10)="") returns zero.

What is wrong ?

Thank you
GIl D.

 

[Gil D.]

Hello,

jasonsweeney - thank you for your help.

I'm trying first Tom Ogilvy suggestion because I prefer using a
formula.

Thank you
Gil D.

 

[Gil D.]

jasonsweeney thank you.

I prefer using a formula so I'm trying (first) Tom Ogilvy suggestion.

Thank you
Gil D.

 

[Tom Ogilvy]

Sorry, had a typo in the sumproduct formula. Should be:

=SUMPRODUCT(--(A1:A10="A"),--(B1:B10=""))

 

[Gil D.]

Hello,

It solved my problem.
Thank you very much for your help.

Gil D.