Help with Excel formula

[donkeymusic]

Hello,

 

I have a spreadsheet which keeps all our assets in place and I'm trying to get a formula that will count how many devices are over 1yr, 3 yr and 5years old. It think I should be using the COUntif formula but no matter what I use I cant get any results and just wondered if there are any excel wizards out there that could possibly offer any assistance.

 

thanks

[Scan099]

Hi, Can you give me an example of your spreadsheet and will see what i can do.

[Boredguy]

Assuming the Age is in Column B, a simple CountIf should do.

If you have 400 rows, and you have the value "1yr" in the cell (other text is available) then drop this in a cell and it should give a result your after.

=CountIf(B2:B400,"1yr")

 

CountIFS would be if you want to have 2 or more ranges of criteria to match

[Seb1780]

A pivot table may be a better option

[donkeymusic]

Thanks,

 

Here is a sample, https://docs.google.com/spreadsheets/d/1Wdxz5aB-SR80nxOc5aWSqmYi_Sh7eRdl/edit?usp=sharing&ouid=117708986358621044668&rtpof=true&sd=true

 

I have it based on purchase dates

[Boredguy]

A pivot table may be a better option

Might be a bit overkill, but also an option.

 

Personally these days for something like what the OP has mentioned, I'd set all that information in Excel to be a Table and give it a name. That way the formulas refer to column headers in the table so you don't have to expand the selection range if you add additional items as the table automatically updates.

[Scan099]

Hi - Is this what you needed?

 

Book1.xlsx

[Boredguy]

Here should be a copy of your example data.

 

Converted your information into a Table by using the "Format as Table" option and called it Assets. Column headers are used in the forumla.

 

New row to get the number of years old the asset is based off todays date.

 

Information in Col L and M is your age range, so it's under 1 year via a CountIf

From 1 to 3 and 3 to 5 with a CountIfs

Over 5 with a Count

Book1.xlsx

[donkeymusic]

Hi - Is this what you needed?

 

[ATTACH]67145[/ATTACH]

 

Thanks for that.

 

That would be useful to get the years, The end goal would be to create a chart and a list to show those devices that are under 1 year, devices between 1-3 years and those that are 3-5 years and then 5 years and above, if that makes sense.

 

thanks

[Scan099]

If you look at Boredguy example it does that - so you could use a combination of both methods to suit your needs.

[donkeymusic]

Here should be a copy of your example data.

 

Converted your information into a Table by using the "Format as Table" option and called it Assets. Column headers are used in the forumla.

 

New row to get the number of years old the asset is based off todays date.

 

Information in Col L and M is your age range, so it's under 1 year via a CountIf

From 1 to 3 and 3 to 5 with a CountIfs

Over 5 with a Count

 

Thanks, that look to do what I need

[donkeymusic]

Thanks everyone for your help.

 

Found a new issue, I had transferred my assets into a google sheets and sheets doesn't support format as a table, so the formulas wont work

[Boredguy]

Ah the downsides of Google Sheets...

You can replace the replace the formats easily enough to be cell references

 

So the "Age" formula would go from =DATEDIF([@DOP],TODAY(),"y") to become =DATEDIF([i2],TODAY(),"y")

The CountIf/CountIFs you would replace Assets[Age] with J2:J9

 

I'm not 100% sure if Google supports the CountIFS as I only use Office365

[donkeymusic]

Ah the downsides of Google Sheets...

You can replace the replace the formats easily enough to be cell references

 

So the "Age" formula would go from =DATEDIF([@DOP],TODAY(),"y") to become =DATEDIF([i2],TODAY(),"y")

The CountIf/CountIFs you would replace Assets[Age] with J2:J9

 

I'm not 100% sure if Google supports the CountIFS as I only use Office365

 

I thought I was playing safe by asking for the solution in excel then just transferring it across.

 

will have a look at your suggestion, much appreciated.

[Boredguy]

Doing a quick search in the Google Sheet Functions, putting the old style cell reference should be all you need to change.

 

They do have something similar to the Excel Table function, but it looks a bit more involved, but as long as you set your cell range to be big enough or you remember to update the formulas as you add items it should work, where as the Excel Table method automatically extended the range to make it easier in the long run.

[donkeymusic]

Doing a quick search in the Google Sheet Functions, putting the old style cell reference should be all you need to change.

 

They do have something similar to the Excel Table function, but it looks a bit more involved, but as long as you set your cell range to be big enough or you remember to update the formulas as you add items it should work, where as the Excel Table method automatically extended the range to make it easier in the long run.

 

Thanks for your time.

 

I have used your suggestion and got it working, just couldn't get it tow rok myself the other day and sometimes you just need the right direction and now it all makes sense.

[donkeymusic]

Afternoon,

 

Sorry to return to this but needing some formula advice again.

 

So from the previous messages i wanted to be able to highlight the age which with your help i was able to do so.

 

What i want to do now is import those items into a new sheet, so using the import range but based on the count if command to highlight just the devices that fall within age range. if that makes sense? so i was using

 

=COUNTIF('Active Assets'!L2:L3000,"<=1")

 

but i need to be able to import them and cant quite the formula right, so wondered if anyone might have any suggestions?

 

thank you

[ConceroEdu_Brad]

Afternoon,

 

Sorry to return to this but needing some formula advice again.

 

So from the previous messages i wanted to be able to highlight the age which with your help i was able to do so.

 

What i want to do now is import those items into a new sheet, so using the import range but based on the count if command to highlight just the devices that fall within age range. if that makes sense? so i was using

 

=COUNTIF('Active Assets'!L2:L3000,"<=1")

 

but i need to be able to import them and cant quite the formula right, so wondered if anyone might have any suggestions?

 

thank you

 

 

You can use the IMPORTRANGE function in combination with the COUNTIF function to import only the rows that meet your specified criteria. The formula would look something like this:

 

=QUERY(IMPORTRANGE("spreadsheet_key", "Active Assets!A2:L3000"), "SELECT * WHERE L <= 1")

 

This formula would import all columns (A-L) from the "Active Assets" sheet in the specified spreadsheet, and then filter only the rows where the value in column L is less than or equal to 1.

 

Another way to achieve this is by using FILTER function:=FILTER(IMPORTRANGE("spreadsheet_key", "Active Assets!A2:L3000"),IMPORTRANGE("spreadsheet_key", "Active Assets!L2:L3000")<=1)

 

This function filter the rows where column L is less than or equal to 1.

[donkeymusic]

You can use the IMPORTRANGE function in combination with the COUNTIF function to import only the rows that meet your specified criteria. The formula would look something like this:

 

=QUERY(IMPORTRANGE("spreadsheet_key", "Active Assets!A2:L3000"), "SELECT * WHERE L <= 1")

 

This formula would import all columns (A-L) from the "Active Assets" sheet in the specified spreadsheet, and then filter only the rows where the value in column L is less than or equal to 1.

 

Another way to achieve this is by using FILTER function:=FILTER(IMPORTRANGE("spreadsheet_key", "Active Assets!A2:L3000"),IMPORTRANGE("spreadsheet_key", "Active Assets!L2:L3000")<=1)

 

This function filter the rows where column L is less than or equal to 1.

 

Thanks, I was using combinations of those but getting nowhere so will work through your advice and hopefully that will get me there, will report back.

 

thank you

[donkeymusic]

You can use the IMPORTRANGE function in combination with the COUNTIF function to import only the rows that meet your specified criteria. The formula would look something like this:

 

=QUERY(IMPORTRANGE("spreadsheet_key", "Active Assets!A2:L3000"), "SELECT * WHERE L <= 1")

 

This formula would import all columns (A-L) from the "Active Assets" sheet in the specified spreadsheet, and then filter only the rows where the value in column L is less than or equal to 1.

 

Another way to achieve this is by using FILTER function:=FILTER(IMPORTRANGE("spreadsheet_key", "Active Assets!A2:L3000"),IMPORTRANGE("spreadsheet_key", "Active Assets!L2:L3000")<=1)

 

This function filter the rows where column L is less than or equal to 1.

 

Good Morning,

 

I have had a look at your advice, and have the following =QUERY(IMPORTRANGE("1QnGk7yUaAZjdzMgOB3YFmYYVe1DBHgfU15-YP8cpp-U", "Active Assets!1:3000"),"SELECT * WHERE L <= 1") but I get the error stating that Unable to parse query string for Function QUERY parameter 2: NO_COLUMN: L but i do have a column L and i tried the column title which is age but still no luck, just wondering if you can see where i have gone wrong. Thanks for your assistance.

[ConceroEdu_Brad]

Good Morning,

 

I have had a look at your advice, and have the following =QUERY(IMPORTRANGE("1QnGk7yUaAZjdzMgOB3YFmYYVe1DBHgfU15-YP8cpp-U", "Active Assets!1:3000"),"SELECT * WHERE L <= 1") but I get the error stating that Unable to parse query string for Function QUERY parameter 2: NO_COLUMN: L but i do have a column L and i tried the column title which is age but still no luck, just wondering if you can see where i have gone wrong. Thanks for your assistance.

 

Does this work?

=QUERY(IMPORTRANGE("1QnGk7yUaAZjdzMgOB3YFmYYVe1DBHgfU15-YP8cpp-U", "Active Assets!A1:L3000"),"SELECT * WHERE L <= 1")

 

- - - Updated - - -

 

Good Morning,

 

I have had a look at your advice, and have the following =QUERY(IMPORTRANGE("1QnGk7yUaAZjdzMgOB3YFmYYVe1DBHgfU15-YP8cpp-U", "Active Assets!1:3000"),"SELECT * WHERE L <= 1") but I get the error stating that Unable to parse query string for Function QUERY parameter 2: NO_COLUMN: L but i do have a column L and i tried the column title which is age but still no luck, just wondering if you can see where i have gone wrong. Thanks for your assistance.

 

Does this work?

=QUERY(IMPORTRANGE("1QnGk7yUaAZjdzMgOB3YFmYYVe1DBHgfU15-YP8cpp-U", "Active Assets!A1:L3000"),"SELECT * WHERE L <= 1")

[donkeymusic]

Does this work?

=QUERY(IMPORTRANGE("1QnGk7yUaAZjdzMgOB3YFmYYVe1DBHgfU15-YP8cpp-U", "Active Assets!A1:L3000"),"SELECT * WHERE L <= 1")

 

- - - Updated - - -

 

 

 

Does this work?

=QUERY(IMPORTRANGE("1QnGk7yUaAZjdzMgOB3YFmYYVe1DBHgfU15-YP8cpp-U", "Active Assets!A1:L3000"),"SELECT * WHERE L <= 1")

 

Thanks for taking a look, but no, i still get the same error

[donkeymusic]

Thanks for taking a look, but no, i still get the same error

 

Think im gettting somewhere now, using the following

 

=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1QnGk7yUaAZjdzMgOB3YFmYYVe1DBHgfU15-YP8cpp-U/edit#gid=1061754958","Active Assets!A1:L3000"),"select * where Col12 =1")

[donkeymusic]

Good Morning,

 

Sorry fo ropening the ticket, but I have since moved to a new school and looking at creating a free asset database, this time going back to Excel rather than Google.

 

The reason for asking the question again is that Excel doesn't have an Importrange formula, and I want to try and create a worksheet with data from another worksheet.

 

So, I have a table that includes a list of IT kit and this also displays the age of the kit.

 

I have countif set up for:

 

=COUNTIF('Active Assets'!L2:L3186,"<=1")

=COUNTIFS('Active Assets'!L2:L3186,">1",'Active Assets'!L2:L3186,"<=3") etc.

 

From Active Assets there is a column that uses =DATEDIF([@[PURCHASE DATE]],TODAY(),"y") that gives the current age of the device.

 

On another worksheet, i need to pull the date from Active Assets to show only those devices that are "<=1",">1,<=3"

 

Ive played around but cant seem to get it to work how i did with Google, which to be fair did exactly what i wanted.

 

So I know several responses last time were from really useful excel wizards, so hopefully someone can advise on this current issue.

 

Thank you

[JohnFury]

I’ve definitely been in your shoes, trying to figure out the best way to count based on dates in Excel. I think you're on the right track with COUNTIF, but it sounds like you need to set up the criteria properly for it to count dates within specific ranges. What I did in a similar situation was use a formula like this:

 

=COUNTIF(A:A, ">"&TODAY()-365)

 

This counts all devices over 1 year old, and you can adjust it for 3 and 5 years by changing the number (e.g., 1095 for 3 years). I also picked up a few great tips from excel.tv on handling date-based conditions that really helped me get a more accurate count.