Summing numbers in an data set depending on a variable

rasmus79

Board Regular
Joined
Sep 4, 2003
Messages
66
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.
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

rasmus79

Board Regular
Joined
Sep 4, 2003
Messages
66
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
rasmus79 said:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,321
Members
414,053
Latest member
Dual Showman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top