Summing numbers in an data set depending on a variable

[rasmus79]

I have tried to solve the following problem with a SUMIF, but I think I am walking down the wrong path....

In Column A I have numbers 0 to 12. There will be several 0's at various points down column A .
Every time a cell in Column A is 0 (not when numbers between 1 and 12) I wish to add up the number 2 collumns over (Column C).
As mentioned, I was not able to solve this with SUMIF, but since the dataset is very big (over 600 rows "long") I hope to find the most Efficient formula... well firstly I guess I hope to find one that works.
Thanks to any who can help.

 

[ken2step]

=SUMIF(A1:A600,0,C1:C600)

This will do it for you!

 

[rasmus79]

That is awesome. I guess I was on the right track - just hadnt quite found the path.
I came into a problem as some of the values I am summing (in collumn C) are N/A, and that "messes up" the sum.
IS there a way I can combine the SUMIF to saying when 0?
I tried this:
=+SUMIF(A1:A600,0*"<>#NA",C1:C600)
But that didnt work, and even if it did work, I realize now that it would only assure that I didnt sum up the numbers in Column C when there were numbers in collumn A that were #NA.... again not quite what I wanted.
If you have any tips on this I would greatly appreciate it.
Thanks

 

[Aladin Akyurek]

That is awesome. I guess I was on the right track - just hadnt quite found the path.
I came into a problem as some of the values I am summing (in collumn C) are N/A, and that "messes up" the sum.
IS there a way I can combine the SUMIF to saying when 0?
I tried this:
=+SUMIF(A1:A600,0*"<>#NA",C1:C600)
But that didnt work, and even if it did work, I realize now that it would only assure that I didnt sum up the numbers in Column C when there were numbers in collumn A that were #NA.... again not quite what I wanted.
If you have any tips on this I would greatly appreciate it.
Thanks

If no #N/A in C1:C600, you can get away with:

=SUMIF(A1:A600,{0,"<>#N/A"},C1:C600)

If C1:C600 and/or A1:A600 house #N/A's...

=SUM(IF(ISNUMBER(A1:A600)*ISNUMBER(C1:C600),IF((A1:A600=0),C1:C600)))

which must be confirmed with control+shift+enter instead of the usual enter.

It would be better to avoid #N/A's in ranges of interest that you want to process.