0

Current G-Sheet

This is for keeping a record of Fantasy Football scores for all players that someone chooses over the season. In the WK (week) column each week a value will be entered. At the bottom for eack WK I only want to add to the total score IF the Value in the In Squad column equals Y. Is this possible please and if so how?

0

2 Answers 2

0

I only want to add to the total score IF the Value in the In Squad column equals Y. Is this possible please and if so how?

The solution is contained in the use of COUNTIF as outlined in the attached Editors help sheet

0

SUMIF

You will want to use the SUMIF function to add values based on a condition. If there are multiple conditions, use the SUMIFS function instead.

SUMIF Syntax
=SUMIF(range, criterion, [sum_range])
Sample Formula
=SUMIF($B$2:$B$8,"Y",C2:C8)

You could then copy the formula from C9 to the WK# columns, D9:F9 in the example, if you wanted to apply the same criteria there as well.

Single Formula: BYCOL

Using a single formula is often easier to maintain than many copied formulas. The formula could calculate the necessary column totals using BYCOL to pass each column in turn into a LAMBDA that would apply the same formula to each of them using the variable col (arbitrarily named) to represent the 'current' column.

The formula could be set to either

  1. Apply SUMIF to 'all' columns, based on the same condition; or
  2. only apply SUMIF to the Total column, but SUM the WK# columns.
1.   SUMIF 'All' Columns
=BYCOL(C2:F8, LAMBDA(col, SUMIF($B$2:$B$8,"Y",col)))
2.   SUMIF 'Total' Column, SUM WK# Columns
=BYCOL(C2:F8, LAMBDA(col,
   IF(COLUMN(C2)=COLUMN(col),
      SUMIF($B$2:$B$8,"Y",col),SUM(col))))

SUMIF 'All' Columns


  SUMIF 'Total' Column, SUM WK# Columns

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.