COUNTIF not working
I have a list of 25 numbers in one column (range). There is one "0" in the list. In another cell when I use =COUNTIF(range, "=0"), I get a value of "1" as you would expect. HOWEVER, when I use =COUNTIF(range, "<1"), I get an output value of "0" rather than "1" which is inaccurate. This is not the only problem. For example, when I input =COUNTIFS(range, ">1", range, "<=3" I am also not getting the expected value. I have already worked with Microsoft to update Excel and when that didn't work, we reset Excel, but that did not help. From other forums I learned that I needed to make sure the cells are formatted as "General" or "Number" rather than "Text," which I have also done, but to no avail. Please advise. Thank you!
Impossible formula? Possibly COUNTIF Function
Hello, I want to create a COUNTIF formula that shows me how many Land Use Designations (LUDs) have building values less than 50% of the total property value. It is basically something like: =COUNTIF(U766:U6886, V>(.5*U)) ROW U is building value, V is total land value (land and building value combined). Does this make sense to any Excel wizards out there? Any help is very much appreciated.
Counting occurrences of Record ID number for use in a calculated column
In my SQL Server db, I have a field that contains a record ID that is repeated on each line of a transaction. Another field contains total cost for the associated transaction that is also repeated. Within the Excel file from which the data is imported, I can use the following equation to create a new column from which I can then use SUMIFS to get the total cost (SUMIFS on the original yields total costs * the number of lines in which the amounts are repeated). The Excel equation is =(SUMIFS(Cost column, ID column, ID number)/countifs(ID column,ID number))*(1/countifs(ID column,ID number). Here is a sample from Excel Equation =(SUMIFS(Q:Q,P:P,P7)/COUNTIFS(P:P,P7))*(1/COUNTIFS(P:P,P7)) ID Number Cost Calc Column Trx Total Proof 12345 1,250.00 416.67 1250 - 12345 1,250.00 416.67 1250 - 12345 1,250.00 416.67 1250 - 67891 1,422.00 284.40 1422 - 67891 1,422.00 284.40 1422 - 67891 1,422.00 284.40 1422 - 67891 1,422.00 284.40 1422 - 67891 1,422.00 284.40 1422 - I would like to add a calculated column to the table that accomplishes the same thing. Each row would then produce a fraction of the total that will add up to the correct amount when aggregated at the ID number level. Is there a way to pass the number of times a field includes a particular result (ID number), to the calculation?