Help With Getting a IF statement to work when using columns referenced from another sheet

TRandolph

Hello all,

I have a sheet that I'm trying to get the elapsed time in days from when a ticket was open to when it was closed. I am doing this using a second sheet, so the formula uses sheet references.

I am having trouble getting this to work when using a range of items. When I use the COUNTIFS function, using areferenced column name works perfectly. I'm however trying to get more data than just the total number, so a count doesn't do me much good.

My working formula, which only works when referencing a specific cell, is

=IF(AND({568 Nature Cell} = "Support", {568 Done Cell} = 1), (DATEONLY({568 Modified}) - {568 Created}), "Not done")

However if I change all of the references in that formula to the respective columns and not the cell itself, I get an "Invalid Operation" error.

Is what I'm trying to do possible? I've done some reading on the Collect function, but I'm not sure how the syntax of those ranges are supposed to be set, nor how to tie it into the above formula.

I would appreciate any help with this issue, thank you!

 

Paul Newcome

What exactly is the purpose of the IF formula? What is the desired outcome?

TRandolph

I only want to get the date difference for items that have a specific nature, in this case support, and that have been checked as Done.

Paul Newcome

Are you trying to add the differences together, or do this calculation on a row by row basis, or...?

TRandolph

Ultimately, for each row in the main sheet that matches both of those criteria (being of a Support nature, and being done) I want to get the total number of days it was opened. I will then be getting an average of these numbers, but I haven't gotten that far yet.

These rows are populated by a form, and the 'done' box being checked is the last action on an item. So I was subtracting the creation dates from the modified date. However the formula only works if I specify the cells in a particular row.

In my example I was using row 568, so the formula has the Nature, Done, Create Date, and Modify Date cells for that particular row. The "if true" portion of the formula does work in this manner, it will return the elapsed number of days fort hat particular item. I want the formula to check this criteria against the whole sheet, and not just a row that I manually specify. When using the COUNTIFS formula it will allow me to choose the entire columns I want to check and thus run over the entire sheet, but COUNTIFS only returns the number of rows that match my criteria. I'm not opposed to using COUNTIFS I just don't know of a way to have the date be calculated in the same call at the same row when it finds a match. I hope this makes more sense, thank you for your assistance!

 

Paul Newcome

Ah. Ok. there are a few ways to do this You will need to get your date differences on the main sheet either way.

 

You can add a column to it (and hide it to keep the sheet looking clean if you want to), and just use a basic 

.

1A.

=DATEONLY(Modified@row) - DATEONLY(Created@row)

.

2A.

or you could use something similar to your formula above to have it only populate the date difference if your criteria is met.

=IF(AND(Nature@row = "Support", Done@row = 1), DATEONLY(Modified@row) - DATEONLY(Created@row), "")

.

From there you have two options on how you want to find the average on your metrics sheet.

.

1Aa.

The first would be using a SUMIFS to add everything up and then dividing by a COUNTIFS to give you how many cells were pulled.

=SUMIFS({Helper Column}, {Nature Column}, "Support", {Done Column}, 1) / COUNTIFS({Nature Column}, "Support", {Done Column}, 1)

.

1Ab.

Or using the COLLECT function to determine your criteria for the cells that will be pulled for an AVG function.

=AVG(COLLECT({Helper Column}, {Nature Column}, "Support", {Done Column}, 1))

.

If you used the formula in 2A. in your helper column, it will decrease how many cells are linked through cross sheet references because we will only need to look at one range.

.

2Aa.

=SUM({Helper Column}) / COUNTIFS({Helper Column}, ISNUMBER(@cell))

.

2Ab.

=AVG(COLLECT({Helper Column}, {Helper Column}, ISNUMBER(@cell)))

.

.

So there you have it. 4 different solutions that will all accomplish the same thing. The one thing they all have in common... The numbers from the date differences that you are wanting to use will need to be on the main sheet that is populated by the form.

 

Hope this helps.

TRandolph

Thank you so much for the response! I will/am going to try these all out, but let me ask this first if you don't mind. I'd tried the solution you proposed in 2A as I thought that would work but I got the Unparseable error. My exact formula (this is the one using the column references) is =IF(AND(@{Ticket Nature}@row = "Support", {Ticket Done}@row = 1), (DATEONLY(@{Ticket Modify Date}@row) - {Ticket Create Date}@row), "")

 

I tried removing the curly braces from the references as well which didn't help it parse. Would you by any chance know why this would be?

 

Paul Newcome

Curly brackets are for cross sheet references. When using a cell reference on the same sheet, if the column name has a space, number, and/or a special character, you wrap in in square brackets. [Column Name]

TRandolph

Yes, these are cross sheet references. I'm using an intermediate 'metrics' sheet to pull data from our tickets sheet.

Paul Newcome

Cross sheet references do not use row references. If they are cross sheet references, then remove the @row. It still not work as you are hoping it will though.

 

The formulas in 1A and 2A are two different options to be used on the main sheet. The one being populated by the form.

TRandolph

I see, thanks again

Paul Newcome

Sure thing. Feel free to let me know if you need any further help getting this set up.