•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Countifs resulting in #VALUE! with error “A value used in the formula is of the wrong data type.”
Our take
When using the COUNTIFS function in Excel to analyze valet performance during peak hours, encountering a #VALUE! error indicates a data type issue within your criteria. In your formula, the timestamps in columns I and AJ may not be formatted consistently, causing the error when combining conditions. Ensure that both columns contain valid time data types.
I’m trying to put together a presentation for a valet company and I’m looking to see how well we perform during peak hours. I have a column for when a vehicle is checked in(column i) and another column for how long it took to retrieve the vehicle(column AJ). I’m using countifs(i3:i2000, “>=06:45”,i3:i2000, “<8:00”,aj3:aj2000, “<00:10:00”) and it results in the problem in the title.
I can do a separate countifs for the i column with the time range above and get a numerical result(65) and another countif for the aj problem and get a numerical result(500) but when combining the two I get the #VALUE! error. What am I doing wrong?
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- COUNTIFS not working correctly and I'm just confused on what to do.I am trying to learn the COUNTIF formula. =COUNTIFS($S$13:$S$61, ">=95", $S$13:$S$61, "<=97") The function counted that there are 2 that fills this criteria; however, I counted it manually and there should be 3 instead. The same goes for other formulas I inputted. I watched Youtube tutorials and read online forums but I still don't get what is wrong with the function. I use Excel 2019. What can I do to improve or solve it? https://preview.redd.it/z08fpbj2olpg1.png?width=442&format=png&auto=webp&s=fb7eeedffeb55ef7649733465c3006b029b9de1b submitted by /u/LoveMilfsEveryday007 [link] [comments]
- Blank cells are being summed as a value greater than five using a SUM(IF functionHi! I have a large dataset of 7-day schedules that I am summing to calculate breaks. The function is setup like this: =SUM(IF(CELL1>5, 0.5, 0)), (IF(CELL2>5, 0.5, 0)) and so on until all seven days are tabulated. Because a full time person would only work 5 days a week, at least 2 days are blank per row, but we're open all 7 days and I want to sum the entire spreadsheet, so I have to count all 7 days. A full time person should have 2.5 hours per week. For some reason the function is mostly working, but about a third of the results are larger than they should be because it's counting some blank cells as greater than 5. For example, a part time person working only 3 days should have 1.5 break hours, but the function returns 3.5 because it is counting all the blank cells as containing a value > 5. What's really strange is it is only doing this some of the time. Every row has at least two blank cells, but only about a third of the sums are wrong. I can't figure out why. The columns are all formatted as a number. The value does update correctly if I manually enter a "0" in the blank cells, but this is a very large dataset and that would take forever. Thoughts? submitted by /u/andylefunk [link] [comments]
- Issue with spreadsheet containing time calculation formulasI have a new clock in/out machine which has 3 columns, 1 for clock in, 2 for clock out and a 3rd column for the total time worked. Staff can clock in/out multiple times a day. It comes to me as an xls file with all the in/out cells as 'general' format and the 3rd column is as custom hh:mm but not the subtraction formula to show the total time worked even though the numbers are right. If i change the in/out from general to custom and then hh:mm I seem to have to click each individual cell for the format to change from (ie) 09:58 AM to 09:58 without the AM but the PM cells still show PM and when i do the subtraction formula for the total time worked it comes out as an error value, no amount of trying different formulas will change it to what it needs to be. I don't mind the first 2 colums showing as AM/PM but even with them that way the subtraction for the total time calculation still comes up as with a 'value' error https://preview.redd.it/hvunwr4p58og1.png?width=512&format=png&auto=webp&s=c11e3a1b77c137a4a40544c7bb2274e813d1038c submitted by /u/Loud-Conference8055 [link] [comments]
- Countif or sumif when adding from a selection of a drop down and the range is in a different columnHi Reddit, I have a column for drop-down selection for machines and another one for time keeping. I'm trying to map out how long each machine is down for and use a pivot chart to make it visual. Any combinations of formulas I come up with come up broken. Any advice is appreciated - even chart visual. TYIA submitted by /u/HellsLamia [link] [comments]