Total Count for month/year from another reference sheet

Options

Hi

I have a sheet named as 2023 and under this sheet there is a total items column that have been entered by user and under the 2023 also have a column which is Month/Year

Right now I am creating a new sheet and want to add the total of items that have been submitted by each month from 2023 sheet data

The formula that I have as below but it giving me error. Pls help to advise.

=COUNTIF({Sheet2023},(Total Item column:Total Item column),{Sheet2023}, "1/2024")

Best Answer

  • Colleen Patterson
    Colleen Patterson ✭✭✭✭✭✭
    Answer ✓
    Options

    @HZAR to confirm, are you wanting to sum the data in 3 ranges where the answer is "1/2023"?

    SUMIFS are designed as follows:

    SUMIFS(range, criterion_range1, criterion1, [criterion_range2​, criterion2​...])

    So the formula is designed to be the following: SUMIFS( the range to be added / summed assuming the criteria are met, the cells to evaluate for the criteria, the criteria, [same thing over again for the other ranges to sum

    Can you confirm that the structure of your order matches the above?

    https://help.smartsheet.com/function/sumifs

    Smartsheet Community Champion and Ambassador

    If my answer helped you, please be sure to mark it as Accepted to help future learners locate the information.

Answers

  • Colleen Patterson
    Colleen Patterson ✭✭✭✭✭✭
    Options

    Count if formulas will allow for a single Range and Criterion for searching that range. If you are looking to do multiple ranges / criterion, you would need to change to COUNTIFS.

    https://help.smartsheet.com/function/countifs

    Smartsheet Community Champion and Ambassador

    If my answer helped you, please be sure to mark it as Accepted to help future learners locate the information.

  • HZAR
    HZAR ✭✭✭
    Options

    Hi @Colleen Patterson

    Sorry not the count I need to sum the total item (under Total Item column) for that month and year and the data is from another sheet…sorry wrong question have been asked

  • Colleen Patterson
    Colleen Patterson ✭✭✭✭✭✭
    Options

    @HZAR There is also a sumifs formula adaption that you would be able to adjust to, based on the information that you are providing. Alternatively, if you would like to add in some screenshots of the sheet, I would be happy to help write the formula for you.

    Additionally in reviewing your formula that you had written, you would need to adjust your brackets on this section: (Total Item column:Total Item column), it should be [Total Item Column]:[Total Item column]. Square brackets are needed around all column names that include spaces or that end in a number or special character.

    Smartsheet Community Champion and Ambassador

    If my answer helped you, please be sure to mark it as Accepted to help future learners locate the information.

  • HZAR
    HZAR ✭✭✭
    Options

    Hi @Colleen Patterson

    If have change it to below, but it gv me unparsable error. May I know where is teh point taht I am wrong here

    =SUMIFS({2023 Oracle Sourcing RFQ Tracker Range 2},[Total Items]:[Total Items],{2023 Oracle Sourcing RFQ Tracker Range 1},"1/2023")

  • Colleen Patterson
    Colleen Patterson ✭✭✭✭✭✭
    Answer ✓
    Options

    @HZAR to confirm, are you wanting to sum the data in 3 ranges where the answer is "1/2023"?

    SUMIFS are designed as follows:

    SUMIFS(range, criterion_range1, criterion1, [criterion_range2​, criterion2​...])

    So the formula is designed to be the following: SUMIFS( the range to be added / summed assuming the criteria are met, the cells to evaluate for the criteria, the criteria, [same thing over again for the other ranges to sum

    Can you confirm that the structure of your order matches the above?

    https://help.smartsheet.com/function/sumifs

    Smartsheet Community Champion and Ambassador

    If my answer helped you, please be sure to mark it as Accepted to help future learners locate the information.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!