Formula to Sum numbers depending on text in other cells

Hello,

Assuming you want to calculate G2 and G3, how will you do this, for example if the status is S, F or O then add the number in the left of it to the sum?

In case you want to sum the number if the cell to the right of it (under Status column) is "S", then this formula might be the one you want

Please Login or Register  to view this content.

Just paste it on G2 and drag it down.

Then it will be something like this

Please Login or Register  to view this content.

The -- in front of a formula will make it return 1 or 0 instead of TRUE or FALSE, in this case I am making the SUMPRODUCT making an array of 0 and 1 with the condition, and multiply it with the corresponding cell to the left of it (that's why the mismatch in cell reference).

Hello Lem,

Those last two formulas will always return zero because all conditions have to be true and the text values in J2:AF2 obviously can't begin with both "S" and "O", you'll need + instead of *, i.e.

=SUMPRODUCT((LEFT(J2:AF2,1)="S")+(LEFT(J2:AF2,1)="O"),I2:AE2)

or you can use SUMIF with wildcards like this

=SUM(SUMIF(J2:AF2,{"S*","O*"},I2:AE2))

I totally forgot about it ... Thanks daddylonglegs for pointing it out, I have fixed the formula on post #6

Do you think both formulas should calculate around the same time when dealing with larger data...

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

Which do you think is best to use?

I suggest using SUM with SUMIF.

Generally COUNTIF and SUMIF are way faster than all kind of array formula that does the same thing (SUMPRODUCT)

Originally Posted by ExcelGal

Which do you think is best to use?

There is calculation timer code at this link:

http://msdn2.microsoft.com/en-us/library/aa730921.aspx

You can test the formulas and see for yourself which is better!