Need quick help counting values from a list for presentation.
Hi All I could usually work this out by searching google and the forum if I had time but its run out so looking for some quick community assistance to get me through the day. I need to count how many times we went to specific locations that are "In area". there is a list of 47 locations and 13 of them are considered "In area" I have a list of jobs in sperate sheets named based on years (2024, 2025, 2026) in those sheets I have a column that lists the location the job was in, which is pulled from a list of locations as a list in the sheet "DataLists". Each year there are approximately 250 jobs and of that 150 are probably in area. In the DataList sheet where the list of locations are pulled from, next to each location listed in column E I have a 1 or 0, in column D, 1 for "in area" and 0 for "out of area". I also have a "Report" sheet that generates a report based on the selected year as a drop down in cell C2 I want to count how many times we went to jobs the were "in area". I tried a quick solution to get me through today and generated a new list in column Q in the DataList sheet with: =IF(D2=1,E2,"") this made a list of only "in area" locations in column Q. Then in one of the year sheets I tried the following formula =SUMPRODUCT(COUNTIF(H3:H200, DataLists!Q2:Q47)) where H3:H200 is the locations of the jobs and DataList!Q2:Q7 is the list of "in Area" and got 3335 which is very wrong as there are only 97 jobs so far in 2026 and maybe 50-60 of those are "In Area" I was going to then expand it to the following in the "Report" sheet. =SUMPRODUCT(COUNTIF(INDIRECT("'"&$C$2&"'!h:h"),DataLists!Q2:Q50)) so it pulls the data from the selected year entered into cell c2 What am I doing wrong here, am I using the wrong function. Sorry only have a few hours before presentation. Ideally I would like the formula in the "Report" sheet, to sum the total number of occurrences from the "year" sheet column H:H for any match from "Datalist" sheet column E:E but only if D:D = 1 I'll take any quick fix at this stage. Cheers Al
XLOOKUP using 3 unique criteria
I have almost completed my pet project, but I have one final piece that I cannot figure out. First off, I work in insurance where I audit incoming (uploaded) spreadsheets from insurance reps. The auditing I do mainly is to be sure that all the data in the uploaded spreadsheet imports into our db accurately and completely. These uploaded spreadsheets list any new, cancelled or adjusted policy information. The policy could be one carrier or it could be multiple carriers. I built a dashboard (sorta) that sorts the rows of data in a way that makes it a TON easier to audit. My main focus here is to list out all the carriers of a policy and to show their cost related columns for each carrier's part. I am using a FILTER formula to list the carriers based off a policy # that is a Data Validation list in cell (Dashboard!A4). The carriers are then listed in cells (Dashboard!AN4 - AN25). Then, in columns (Dashboard!AO - AS) are the cost related columns. The Data Validation list, Carriers and cost related columns are all pulled from the Transactions worksheet. So far, this is working MOSTLY as intended, but because some policies use the same carriers, the cost related columns are not filtering by policy and will then therefor show the first available entry for each carrier (kinda like what VLOOKUP does). I am using the following formulas so far. List of carriers: =FILTER(Transactions!H:H, Transactions!A:A=A4, "No matches found"). This works as intended. Cost related columns: =XLOOKUP(AN5,Transactions!$H$3:$H$1000,XLOOKUP(Transactions!$O$2,Transactions!$A$2:$BA$2,Transactions!$A$3:$BA$1000)). This too works as (mostly) intended, but as you can see, I need an additional filter to sort by policy #. This should be the last step to finishing this pet project. I would appreciate it if anyone can help me add the additional formula criteria that would now filter out by specific policy. I tried adding another XLOOKUP to the formula to add the additional criteria based on policy # with no luck. For additional reference, the Transaction worksheet will list the same policy for each row that contains a unique carrier. So if a policy has 5 unique carriers, there will be 5 rows to list each unique carrier and their cost related amounts associated with each carrier (row). What I need is to add the additional filter based off the policy # to be sure that the cost related columns are specific to the policy # and individual carriers? Any help would be GREATLY appreciated. Thanks
Office Scripts
i have a business microsoft office account and am signed in with it. when i try running a script in excel i keep getting the error saying it requires a work or school account. my microsoft account says the following: your work or school account belongs to your home organisation. what do i need to do to get scripts working.
Conditional formatting cannot be displayed when pasting using the camera method.
When I paste the table into a different area (using the camera method), the formatting for some cells is not properly displayed. These cells have “Conditional Formatting” applied, and their colors change based on their content. When I make changes to the original table, I can see that there is no issue and the colors update correctly. This problem also occurred when I used the table as a “linked image.” Visual Explanation; (The original table is on the left; on the right is the same table pasted using the camera method. Both are in the same file and tab.) When I copy and paste the hidden rows separately (using the camera method again), the conditional formatting appears. In other words, there is no specific issue with the rows where the “formatting styles” cannot be displayed properly: Unfortunately, I didn’t get any results when I tried the “shake” method. When I tried it on a new worksheet, the result remained the same. I also tried the “Repair Office” option, but the problem persists. Could there be a row height limit when copying this type of table using the “camera tool” (when capturing screenshots of conditional formatting) When the total row height in the tables exceeds a certain value, the “conditional formatting” properties of the table I move to another location using the “camera tool” do not display. When I copy 26 rows to another location with each row set to 32.75 units in height, all properties are displayed; however, when I change it to 33 units in height, the formatting starts to break. And this total value (26 × 32.75 = 851) isn’t consistent either. When I try to set individual height values for the cells to reach a total height of 851, the issue isn’t resolved. Sometimes I get proper display when the total height is 777, and sometimes when it’s 661. What do you think is the root cause of this problem I’m facing? How can I solve it? Office Version Information: Ref: https://learn.microsoft.com/tr-tr/answers/questions/5861040/excelde-tabloyu-ba-l-resim-olarak-yap-t-ramama-sorExport mixed text and tabular Excel to PDF
I have a sheet that I need to export from Excel to PDF. The sheet contains text and tabular data. Ideally, I'd like large bold text to export as heading tags, text as paragraph tags, and the tabular data as a table. Currently, any group of cells containing data that are surrounded with blank cells export as a table, even if they don't contain what a human would consider tabular data. I haven't selected the tabular data and chosen Import > Table. I'm not sure if that would help or not. How can I export mixed text and tabular data from Excel and have it be tagged as desired in the resulting PDF?
Python integrado con excel
Tengo una suscripción de Microsoft 365 Empresa Estándar, ya estoy dentro del grupo de Microsoft Insider 365, tengo habilitado el Canal Beta pero aún así no me esta funcionando Python integrado con excel ya que escribo el código pero no me muestra el resultado, en su lugar me muestra el mensaje "BLOQUEADO" indicando que no tengo la licencia requerida. He hecho de todo lo que me ha salido de consejos en la web, incluso cerré sesión y volví a ingresar pero el resultado es el mismo:Error Opening the Excel
I can't open the file Get Message. Saved in Last time. Previous Version history Not available. Copy from Cloud to pen drive no difference. Try in 365 online mode not open. Try with google sheet not Open. Change the file extension to .xls & .csv not open. Online Platforms not open. Change the Computer and Operating System. Try to open WPS; But no change.Why are documents now displaying tiny cells and none of my cell text is visible.
Opened this application today to find this. The view is damaged or corrupted so that there are hundreds or thousands of cells visible in the viewport at 100% view scale. Zooming in does nothing to improve this. Zooming out to 10% then appears to show some of the text very small in the top left of the viewport, until zoomed in again at which point the text disappears once more. What is visible is very clear in the screenshots attached to this post. We have attempted an online repair of microsoft 365. this has done nothing. All existing documents persist in showing this. All new documents persist in showing this. We do not know what windows updates have been done. Nothing has been done by us. We do not know if any changes to drivers has occurred. Nothing has been done by us. We are looking for advice or knowledge from any user/s who have seen this specific issue before.
Delete a print area label on a worksheet
Hi, I would like to delete the print area labels on a worksheet. I find that the labels I used are not descriptive enough. Excel names the first print area as Print_Area. The next area would be labeled Print_Area_1. I would like to delete all of the labels or perhaps selected labels so I can enter a label name such as Print_Area_1_SUN. Would appreciate your help with this. Joe
