asked on

# Countif might work??

Is there a way to add to my formula in cells A9 through B30 so that only one occurrence of the acct# and act code list? You can see because on the daily sheet there are multiple occurrences of them they are listing multiple times in the timecard sheet therefore adding additional hours in the timecard sheet for Monday in this case.

**No credit card required.**

ASKER

That's a cool formula how do copy it down so I can use it for the rest of the timecard sheet? I tried just copying it into the other cells and got a spill error

The formula is designed to be placed in a single cell and spill its results down. You do not need to copy it down.

A #SPILL! error usually means you need to clear the cells where the results need to go before entering the formula. It won’t spill if constants or other formulas occupy those cells.

ASKER

when I enter this formula how does it work? I don't see how i define it to populate anything on the timecard sheet for columns a&b. I do see like this blue line

In September 2018, Microsoft introduced a new feature called dynamic arrays by which a formula "spills" an array of results into cells below and to the right of the cell where you paste the formula. If you have a version of Excel that supports dynamic arrays, you no longer need to Control Shift Enter for array formulas.

The suggested formula uses the UNIQUE and FILTER functions. These functions are designed to return more than one result value. In fact, Microsoft was clever as those functions return an array of results the exact size it needs to be, with one result value per cell. The blue border you notice shows you the extent of the spilled values being returned.

ASKER

so all i have to do is copy that formula into cell a9 and it magically does the rest?

Correct! One formula goes in one cell (A9) and gives you multiple results, down and to the right of cell A9.