CODEWITHSUNDEEP
stringexcelexcel-formulaexcel-2007
Gokotai
Gokotai

Reputation: 153

Excel formula for conditional summation based on string

I have a column of data that looks something like this:

Row A B C D 100 50 0 A->B 50 0 100 C->A 75 75 0 B->A etc

I want to enter a formula in a single cell that sums across the rows A to C only if the first letter in column D is "A", but for the range e.g. A2:C10000.

Is this possible?

As things stand, I have dabbled with the LEFT() function but I cannot get it to work across a range.

Upvotes: 1

Views: 107

Answers (1)

Robert Mearns
Robert Mearns

Reputation: 11996

You can use the SUMIF formula to sum a single column where a cell in Column D starts with A.

=SUMIF($D$2:$D$10000,"A*",$A$2:$A$10000)

To get all three columns repeat the formula three times.

=SUMIF($D$2:$D$10000,"A*",$A$2:$A$10000)+SUMIF($D$2:$D$10000,"A*",$B$2:$B$10000)+SUMIF($D$2:$D$10000,"A*",$C$2:$C$10000)

Upvotes: 2

Related Questions