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?
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.