I need a formula for a report

americanfederal

Need a count if formula and sum if for to look at certain sheet and populate another sheet:

EX. If I have a row that has a cell with certain name like Event1 then has appt set checkbox and sales amount

I want to count how many appointment set checkboxes there are with the name Event1

Also same example Sum a Sales amount if the row contains a name like Event1

Antonio Figueroa

Hello americanfederal 

I would use a copy a row Automation. Set the automation to copy rows that has event 1 and appt set checkbox and sales amount to a new sheet that you have to create before creating this automation.

Then you can run your countif's and sumif's because the only rows that will be on the sheet are the ones that match your criteria.

Hope that helped

Steve@PGI

American Federal - You can definitely meet your requirements with =countifs and =sumifs formulas. I use these all the time consolidated on a Metrics Sheet to calculate metrics and then present them on dashboards. The =countifs and =sumifs allow multiple criteria as you requested. The formula wizard will help walk you through building these if you haven't done them before.

Kelly Moore

Hey @americanfederal

Building on Steve's advice

In the destination sheet (the sheet you want to show the countifs and sumifs values) put this formula in the appropriate column. Don't forget, when building a cross sheet reference, you cannot copy paste the formula from here - you will need to build the cross sheet references by clicking the blue cross sheet reference link in the formula window. You'll see it after you type =COUNTIFS( or =SUMIFS( in a cell. It will be right below the formula wizard Steve referred to.

If you need more info on cross sheet references you can find it here

(Note- a good practice is to rename the cross sheet reference with the appropriate column name rather than keeping smartsheets generic range# name. This will help you and the community trouble shoot formulas more easily.)

Each Source sheet (the other sheet that has all the data) range is a single column.

=COUNTIFS({source sheet Event column}, Event@row, {source sheet checkbox column}, 1)

Notice the COUNTIFS function has the syntax range1, criteria1, range2, criteria2, range3, criteria3, etc. You can add as many or as few criteria as needed but you'll always add them as a range-criteria pair.

SUMIFS is almost the same, however it has the additional term of the range that you would like summed.

It's syntax is SUMIFS(range to be summed, range1, criteria1, range2, criteria2, range3, criteria3, etc.)

=SUMIFS({source sheet Sales column}, {source sheet Event column}, Event@row, {source sheet checkbox column}, 1)

cheers,

Kelly