I'm setting up a spread sheet to keep track of fire calls that volunteer firefighters respond to. We require that they make a certain % of calls per quarter to stay on the fire department. Data is recorded on a spreadsheet where the first two column headings are "line number" and "Firefighter name" and then there is a long list of fire incident numbers which is basically a unique number after the 4 digit year (2010001, 2010002, and so on). I put a 1 if the firefighter responded to that call and a 0 if they did not. Sounds pretty simple so far doesn't it?

The problem comes in when I need to separate this long line of fire calls up into 4, 3 month segments and add the calls up for each quarter/firefighter. I could have 20 fire calls in 3 months but I might also have as many as 50 so the start and end points for each quarter will be different from year to year and I don't want to re-do all my formulas every year.

I've setup another tab/sheet called "Start Here" which whoever inputs the data will be asked to provide the last incident number for each quarter. I had planned on using this to compensate for the variable start and end locations for the SUM formula for each quarter.

So what I've tried so far (I'll brake up the formula cause it's kind of long):Formula 1: =ADDRESS(ROW('Calls and Trainings'!C6),MATCH('Start Here'!B16+1,'Calls and Trainings'!A5:FF5,0),4)

Formula 2: =ADDRESS(ROW('Calls and Trainings'!C6),MATCH('Start Here'!B21,'Calls and Trainings'!A5:FF5,0),4)

Both of these formulas do the same thing. They both search the column headings for the last incident number of the quarter provided by the user in the Start Here Tab. Once they find it, they each give one of the cell addresses I need for the SUM formula. The only diff between the two is the first uses the previous quarters last incident number and adds one to get the first incident in the this quarter. Both of the above formulas work by themselves but when I go to put them in a SUM formula like so . . .

Complete Formula: =SUM(ADDRESS(ROW('Calls and Trainings'!C6),MATCH('Start Here'!B16+1,'Calls and Trainings'!A6:FF6,0),4),ADDRESS(ROW('Calls and Trainings'!C6),MATCH('Start Here'!B21,'Calls and Trainings'!A6:FF6,0),4))

When I put this formula in it gives me this error: "A value used in the formula is of the wrong data type."

What I'm thinking is going on is that Formulas 1 and 2 above are giving outputs in a way that the SUM formula can't process them as cell addresses/locations. I don't know how to fix this nor am I sure if I can fix it.So my question is, can I fix the above formula so that it works or do I need to find a different way of doing it?

Thanks for the help.

Hi, If incident dates are placed in row 1, starting at column C (you have line # & firefighter in cols A & B), and the first firefighter name is in row 3 (cell B3)

then in a results area, create four pairs of headings with start and end dates for each quarter.

If the first quarter dates are in cells AA1 and AA2 enter this formula in cell AA3

AA3 is the same row as the first firefighter's name:=SUMPRODUCT(($C$1:$N$1>=AA$1)*($C$1:$N$1<=AA$2)*($C3:$N3))This will count the 1's on row 3 underneath all the incidents, for the specified quarter.

For this example the incidents were in columns C to N, but they can extend as many columns as required.SUMPRODUCT() will only sum the values where the criteria are matched, in this case on or after a start date AND on or before a finish date.

It doesn't matter if there are empty columns, so set up SUMPRODUCT () to use as many columns as you might have fire calls.Drag the formula down as many rows as there are firefighter names.

Add a pair of dates for each quarter in cells AB1/AB2, AC1/AC2 & AD1/AD2

Drag the SUMPRODUCT() formulas to the right to extend them to four columns and you will have the four quarter totals.Note that all dates must be in an Excel recognized format.

Hope this helps.

Regards

I tried to set up a workbook to make your formula cause the "data type" error, but the best (worst?) I could do was produce a #VALUE error. I solved that issue by using modifying your formula as follows:

=SUM(INDIRECT(ADDRESS(ROW(Sheet1!C6),MATCH

(Sheet2!B16+1,Sheet1!A6:FF6,0),4)),INDIRECT(ADDRESS

(ROW(Sheet1!C6),MATCH(Sheet2!B21,Sheet1!A6:FF6,0),4)))The reason for those changes is that the ADDRESS functions were returning things that looked like this:

=SUM({"A6"},{"G6"})

By adding INDIRECT to this, I was able to actually SUM the values in those cells.

P.S. The way I determined that was via the little known F9 feature. If you highlight a portion of a formula in the Formula Bar and hit F9, Excel will evaluate that portion of the formula right in the Formula Bar. You just have to make sure you highlight something that Excel can evaluate. In this I case I highlighted:

ADDRESS(ROW('Start Here'!C6),MATCH('Calls and Traingings'!B16+1,'Start Here'!A6:FF6,0),4)

Thank you very much for the responses. I went with the first response and it works great. I did tweak it a bit so that all the required formula is contained in one cell. Final Formula: =SUMPRODUCT(($C$6:$FF$6>='Start Here'!$B$16+1)*($C$6:$FF$6<='Start Here'!$B$21)*($C7:$FF7))

Again, thanks a bunch.

Hi, Glad to hear you got it to work and thanks for the feedback.

Regards

Ask Your Question

Weekly Poll

Have you played Jackbox during the pandemic?

Discuss in The Lounge

Poll History