CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Luis
Luis

Reputation: 95

Make a script to conditionally sum cells

I want to make a small script in google script editor for spreadsheet that sum's cell's according to other cell. Something like:

IF (A2:P2)==Q2{
   SUM (A3:P3)}

I want to sum the cells that contains a certain character. Here a example of my question. How can I do it?

Upvotes: 0

Views: 553

Answers (2)

JPV
JPV

Reputation: 27262

To make the sum (row per row) of all the columns that have 'A' in the header, try in Q4:

=ArrayFormula(MMULT(N(IF(B3:P3="A", B4:P11,)) , transpose(N(B3:P3="A"))))

Change 'A' to 'B' etc.. for 'sum of all B'

If you want the result to auto-expand when new rows are added use the formula like this:

=ArrayFormula(IF(LEN(A4:A), MMULT(N(IF(B3:P3="A", B4:P,)) , transpose(N(B3:P3="A"))),))

EDIT: based on new info, I updated the formula to:

=ArrayFormula(MMULT((B2:P2=row(B3:B10)-2)*(B3:P10="A"), TRANSPOSE(COLUMN(B2:P2)^0)))

Example sheet

Of course the SUMIF() proposed by Matt will equally work (and is definitely a lot simpler). To drag the formula down, use the dollar signs to 'freeze' the header row, e.g: sumif($B$3:$P$3,"A",B4:P4)).

Upvotes: 2

Matt
Matt

Reputation: 46710

You don't need a script for this. You are just describing SUMIF which

Returns a conditional sum across a range.

Using a copy of your sheet I got your desired result with this formula

=sumif(B3:P3,"A",B4:P4)

Then you could use absolute cell references to make it easier to copy this down.

=sumif($B$3:$P$3,"A",B4:P4)

Question has changed

Since you have changed the source sheet you are asking a related but different question. Same as before though... you don't need a script for this. You can use COUNTIFS(). You have two counting criteria. Count if the cell contains a letter and count if the header row contains a number. We break this up into two conditional sets. 

=COUNTIFs(B3:P3,Q2,B2:P2,1)

Q2 is the cell that contains "A" and the 1 is the header columns we are counting.

In order to move this down the row by simple drag and drop you still need to use absolute reference in the cells that are not moving. Q2 and the header row specifically. 

=COUNTIFs(B3:P3,$Q$2,$B$2:$P$2,1)

Upvotes: 2

Related Questions