totalling columns when both are populated

[carsto]

I want to total column A & total column C but only if there is an amount in both cells on the same row
Can I do this with an array?
Do the columns have to be adjacent? I could make them if necessary.

Data
A C
11 12
21 0
0 17
15 15
11 9

in this example we exclude rows 2 & 3 from the total because one cell contains zero or is blank.
Total Column A = 37 (11+15+11)
Total Column C = 36 (12+15+9)

[MDubbelboer]

in a: =SUMIF(C3:C7,"<>0",A3:A7)
in c: =SUMIF(A3:A7,"<>0",C3:C7)

adjust the a3:a7/c3:c7 ranges as required

[dacius1]

so you want a vertical solution to a horizantal qualifier?

Try this

Make another column (my example will call it column D)
=if(and(a1>0,c2>0,1,0)

polulate that formula down.

Then do a sumif at the bottom of each column

=sumif(d1:d5,1,a1:a5) for column A
=sumif(da:d2,1,c1:c5) for column B



see if that works. It worked in my little test.

[carsto]

mark,
yours doesn't work because it only looks at B to total A and vice versa

dacius1
I'd like to do this without an additional column (I have too many columns already)

what I'm looking for is something like this:
=SUMIF(A2:B6,AND(B2:B6>0,A2:A6>0),A2:A6)
but this returns Zero

[Maistrye]

Not to be rude, but I believe if you consider Mark's solution, you'll find it does what you asked -- maybe not what you wanted -- but it does do exactly as you said you wanted (in your first post in this thread)

If you wish for an explanation of why it works, that can be provided.

The only slight modification you've made to your second post is that you limit it to values that are greater than zero.

Please verify your specs.

Thanks,
Scott

(Editted to remove my incorrect change to Mark's for the different specs)

[carsto]

my apologies, I'll try to be clearer.
I want to total column A & total column C but I don't want to include any row that has no entry (or zero) in either column

Data
A C
11 12 this row is included in both counts
21 0 this row is EXCLUDED in both counts
0 17 this row is EXCLUDED in both counts
15 15 this row is included in both counts
11 9 this row is included in both counts

in this example we exclude rows 2 & 3 from the total because one cell contains zero or is blank.
Total Column A = 37 (11+15+11) exclude 21 & 0
Total Column C = 36 (12+15+9) exclude 0 & 17

BTW, all numbers are positive so <0 isn't necessary (i think)

When I use Marks formula SUMIF(C3:C7,"<>0",A3:A7)
I get a total of 58 for Column A, which include the 21 I want excluded)

[Maistrye]

That is definitely weird... I put the values in as you stated, and Mark's formulas, and I got 37 and 36 respectively.

I don't know what to tell you... what version of Excel are you using?

In any case, here's an alternate formula that will do the trick as well:

=SUMPRODUCT(--(A3:A7>0),--(C3:C7>0),A3:A7) --> Totals Column A
=SUMPRODUCT(--(A3:A7>0),--(C3:C7>0),C3:C7) --> Totals Column C

This allows for > 0.... you can make it <> 0 if you'd like.

Scott

[MDubbelboer]

my apologies, I'll try to be clearer.
I want to total column A & total column C but I don't want to include any row that has no entry (or zero) in either column

Data
A C
11 12 this row is included in both counts
21 0 this row is EXCLUDED in both counts
0 17 this row is EXCLUDED in both counts
15 15 this row is included in both counts
11 9 this row is included in both counts

in this example we exclude rows 2 & 3 from the total because one cell contains zero or is blank.
Total Column A = 37 (11+15+11) exclude 21 & 0
Total Column C = 36 (12+15+9) exclude 0 & 17

BTW, all numbers are positive so <0 isn't necessary (i think)

When I use Marks formula SUMIF(C3:C7,"<>0",A3:A7)
I get a total of 58 for Column A, which include the 21 I want excluded)

hmmm...i don't understand how you'd get 58 from that formula. It definitely returns 37 when i inputted it.
essentially from the first formula it'll include anything from A where C<>0, so the second row is eliminated. the third row is not eliminated however it's value is 0 so it's inclusion in any sum formula will not affect anything.

likewise in the second formula the 3rd row is omitted, but the 2nd row is not. again it's value is zero so it does not affect the sum.

You do have the cell references setup properly for your data? i assumed from your column Headers that we were talking about columns A and C...

[carsto]

Thank you BOTH

**MARK**
I know now why Marks' didn't work

I put ZEROs in my example, but not all the cells have data, some are just blank.
If there are Zeros in all the cells, then Marks works.

However, I don't understand why Marks works
SUMIF(C3:C7,"<>0",A3:A7) it looks like you're only telling it to ignore the blanks in column C
Now I read the explanation above, thanks!

**SCOTT**
Scotts works with truely blank cells BUT my cells are blank because an IF formula returned "" in the cell

To complicate matters, in my little test worksheet Scotts formula works, but in my real spreadsheet =(SUMPRODUCT(--(DZ8:DZ376),--(EA8:EA376>0),DZ8:DZ376)) it's a different story
when I change my IF statement to return 0 instead of "", this formula returns $39 Billion plus, when the correct result is $1,772,429

& I'd love an explanation of this formula

For now, I will proceed with the adjusted IF statement and Marks formula
THANKS

[MDubbelboer]

=(SUMPRODUCT(--(DZ8:DZ376>0),--(EA8:EA376>0),DZ8:DZ376))

you missed a >0 in there
essentially a sumproduct tests a number of conditions in a row and sums the product of each of these conditions.

you can look at it as 368 seperate lines being summed together.

For the formula at hand we first look at row 8
Is DZ8>0, if so True, if not False
the -- at the beginning converts true to 1, and false to 0
second condition:
Is EA8>0, if so true, if not false
again the double negative makes it numeric.
third condition:
dz8=value of dz8

so if condition 1 = true and condition 2 = true our sumproduct for the first row is returning
1*1*value of dz8=dz8
if either condition was zero we would have 0
0*1*value=0

So essentially if all criteria were met in all rows our answer would be the sum of DZ8:DZ376.

Since you missed that first >0 sign it would essentially be giving you
Value of DZ8*1*Value of DZ8
so for every value of EA greater than zero you would get the sum of squared DZ's. which is why your number was so big

hope that helps

[carsto]

oops! Thanks, got it

I LOVE EXCEL
the more you learn the more you realize just how little you know

& the potential is without limit!

[MDubbelboer]

the more you learn the more you realize just how little you know

So True.

Sumproduct is very very useful for many applications. getting comfortable with it will save you plenty of headaches down the road