Copy a NB.SI formula into a Google Sheets sheet

0
votes

I am turning to you today for help with a problem on Google Sheets.

I receive this data from a Google Sheets form: An answer (0 or 1) to 5 different questions. I would like to calculate in column A (in green) the scores out of 5 for each row, as soon as a new row is added by the form.

Sheet without notes

I tried to use the ARRAYFORMULA() function but it does the count for all the cells in the range and not just row by row:

enter image description here

Do you have an idea to have a score out of 5 for each line of question and have it apply to the whole file as soon as a new line is added by the Google Form?

Thanks for your help

2
google-sheetsgoogle-sheets-formulaarray-formulas

2 Answers

1
votes

If you want to use COUNTIF (English correspondance for NB.SI), modify your formula to:

=ARRAYFORMULA(COUNTIF(IF(B1:F=1,ROW(B1:B)), ROW(B1:B)))

or for your regional settings:

=ARRAYFORMULA(NB.SI(IF(B1:F=1,ROW(B1:B)), ROW(B1:B)))

1
votes

You can get a row-by-row sum with sumif() like this in cell A3:

=arrayformula( sumif( if(column(B3:F), row(B3:F)), row(B3:F), B3:F) )

This formula uses open-ended range references so it will create results all the way down to the end of the sheet. To limit that, use a range reference like B3:F100 instead.