Formula Question

  • Thread starter Thread starter Crystal
  • Start date Start date
C

Crystal

I'm trying to total numbers that match certain cells as in the
following example:

A B C

26 x
34 y
-117 z
120 z
2 x
14 y
39 z

I want column C to reflect the running total for A as it relates to Z.
So column C would read:

C
26
26
-91
29
29
68

Hope I explained it well.

Thanks
 
I'm not sure why C1=26, since it's an x value not a z value...

In general I'd say put this in C1:

=SUMIF(B$1:B1,"x",A$1:A1)

and copy down.
 
I'm not sure why C1=26, since it's an x value not a z value...

In general I'd say put this in C1:

=SUMIF(B$1:B1,"x",A$1:A1)

and copy down.
Click to expand...

Thank you. You found my error, but your solution works fine.

Much appreciated.
 
Hello,

I suggest to let the data start in row 2.

Then you can into C1:
z

Into C2:
=IF($B2=$C$1,$A2,0)

Into C3 (and then copy this down as far as necessary):
=IF($B3=$C$1,$A3+C2,0)

If the table is small the speed of our formulas seems neglectable.
But for a large table this formula is by far quicker (about 80 times
quicker for a table with 3000 rows).

Regards,
Bernd
 
Hi Crystal,

I think the formula should be

=SUMIF(B$1:B1,"Z",A$1:A1)

If you get it to work with x then I don't understand your question.

If your first row of data is on row 2 or lower you can use something like

=IF(B2="z",A2)+C1

Cheers,
Shane
 
Hello,

I suggest to let the data start in row 2.

Then you can into C1:
z

Into C2:
=IF($B2=$C$1,$A2,0)

Into C3 (and then copy this down as far as necessary):
=IF($B3=$C$1,$A3+C2,0)

If the table is small the speed of our formulas seems neglectable.
But for a large table this formula is by far quicker (about 80 times
quicker for a table with 3000 rows).

Regards,
Bernd
Click to expand...

Thanks. Could you explain your formula a little. Specifically the 0
at the end. I don't quite follow that.

Much appreciated.
 
Hello,

There was a typo in the C3 formula, sorry. Substitute 0 by C2, please.

Regards,
Bernd
 
Back
Top