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?
