Formulas and Functions

Stumped by a formula? Ask for help or find examples to see how others use a formula to solve a similar problem.

Discussion List

  • Rank Top 20 Clients By Value of Won Opportunities
    In Formulas and Functions
    We have a sheet we use to track opportunities. I'm trying to figure out a formula to create a list of top clients by the value of won opportunities in a metrics sheet so that I can add the list to a dashboard. Above is an example of the da…
    Answered - Pending Review
    12 2
    Answered - Pending Review 12 views 2 comments Most recent by Sarah Dawson
  • Stumped on combining AND/IF/OR formula
    In Formulas and Functions
    I need a formula where if Innovate Audit is Yes, or Innovate Audit Upgrade is Yes, but Pull Print is No, then the the # of devices requiring in printer agent/40 +1 Here is what I came up with so far... =IF(OR([Innovate Audit]@row = "…
    Accepted
    20 5
    Accepted Answer 20 views 5 comments Most recent by Jason Albrecht
  • Counting Concurrent Jobs
    In Formulas and Functions
    Each row in this spreadsheet is a "Job". I'm trying to count (project the count by month) concurrent "Active" jobs (rows) based on a start and finish date of the job. I created two columns (per 12 months) to set the cr…
    Accepted
    11 4
    Accepted Answer 11 views 4 comments Most recent by Andrée Starå
  • How to find the largest Items in a list and their corresponding Item names?
    In Formulas and Functions
    Hi, I'm trying to identify the TOP 5 most Requested Items in my Inventory list. I have a formula in place that finds the value of the Top 5 (Formula #1); however, my formula (Formula #2) that finds the corresponding Item Names of the TOP 5…
    Answered - Pending Review
    14 1
    Answered - Pending Review 14 views 1 comment Most recent by SoS | Dan Palenchar
  • Is there a formula that will automatically add zeros in front of numbers added to a cell?
    In Formulas and Functions
    Is it possible to have this number show, 000000065145, while only typing in 65145? I need to have seven leading zeros before the number.
    Accepted
    16 2
    Accepted Answer 16 views 2 comments Most recent by Andrée Starå
  • COUNTIFS referencing date ranges specified in Sheet Summary
    In Formulas and Functions
    I'm trying to count the number of cells (dates) that fall between two dates that are selected in sheet summary. In excel I've been able to make this work =COUNTIFS($C$7:$C$47,">="&$H$1,C7:C47,"<="&$H$2) H…
    Accepted
    16 5
    Accepted Answer 16 views 5 comments Most recent by Andrée Starå
  • How to add percentage with a blank cell and get the result in percentage format?
    In Formulas and Functions
    Hello everyone, I am adding 2 different Efficiencies to show the Total efficiency. When there are values in the cell it works just fine, but when I am adding a percentage with a blank cell (or even a Zero) , the final result doesn't get co…
    Accepted
    13 3
    Accepted Answer 13 views 3 comments Most recent by Lidiya S.
  • Sheet Summary formula for counting unique values within a multi-select dropdown column?
    In Formulas and Functions
    I am trying to count the number of unique systems used within a workflow. I have dozens of possible internal systems listed within a restricted, multi-select, dropdown menu. As these systems are populated to the grid (sometimes multiple wi…
    Accepted
    49 5
    Accepted Answer 49 views 5 comments Most recent by Ruth wright
  • Status Formula
    In Formulas and Functions
    Hi all, I am struggling to create a formula that works and wonder if you can help me….   Duration Equal to or Less than 0, Gray. Duration Greater than 0 but “Email Sent” & “Return Old Device” unchecked, Yellow. Duration Greater than 2…
    Answered - Pending Review
    19 3
    Answered - Pending Review 19 views 3 comments Most recent by Andrée Starå
  • Would Like to Copy a value from one location to another weekly
    In Formulas and Functions
    Hello! I have 2 sheets SHEETA and SHEETB. SHEETA has a ton of data in it and I want to take a value from one cell inside or the sheet summary it and copy it weekly to another sheet. I know I can copy rows but that is not an option for me. …
    Accepted
    7 4
    Accepted Answer 7 views 4 comments Most recent by Christopher Neal