The title of this post may be confusing so ill go into as much detail as possible.
Where i work, i constantly have to calculate revenue based off of claims. I run a report every month and i get many columns worth of data. This data is copied and pasted into its own tab in a workbook. I then reference that data with a COUNTIFS formula on another tab. this is the current formula i have constructed:
"=COUNTIFS(INDIRECT("'"&$E$2&" Claims'!A:A"),$A$26,INDIRECT("'"&$E$2&" Claims'!B:B"),$D$26,INDIRECT("'"&$E$2&" Claims'!D:D"),E27, INDIRECT("'"&$E$2&" Claims'!G:G"),I9)+COUNTIFS(INDIRECT("'"&$E$2&" Claims'!A:A"),$A$26,INDIRECT("'"&$E$2&" Claims'!B:B"),$D$26,INDIRECT("'"&$E$2&" Claims'!D:D"),E27, INDIRECT("'"&$E$2&" Claims'!G:G"),J9)+COUNTIFS(INDIRECT("'"&$E$2&" Claims'!A:A"),$A$26,INDIRECT("'"&$E$2&" Claims'!B:B"),$D$26,INDIRECT("'"&$E$2&" Claims'!D:D"),E27, INDIRECT("'"&$E$2&" Claims'!G:G"),K9)+COUNTIFS(INDIRECT("'"&$E$2&" Claims'!A:A"),$A$26,INDIRECT("'"&$E$2&" Claims'!B:B"),$D$26,INDIRECT("'"&$E$2&" Claims'!D:D"),E27, INDIRECT("'"&$E$2&" Claims'!G:G"),L9)"
In this formula, the tab im referencing every month is duplicated, and renamed according the current month and the new months data is imported monthly. thats why im using an INDIRECT function to reference the tab rather than a direct reference to one particular tab. cell $E$2 is where i input the month name and its joined with the word "Claims". in these tabs, Column A is where the Office name for every claim is listed, Column B is where the Line of the claim is listed (Workers Comp, Disability, etc). Column D is where the claim type is listed (Bodily Injury, Property Damage, etc). and column G:G is where the state of jurisdiction is located. This is where things get tricky. You can see in the formula that the cells I9, J9, K9, & L9 are referenced separately. ex: in the cell I9 is the letters CA. as you can see in this formula, for every state that i need to reference, i have to repeat the entire formula just to reference another additional State. Its important that i count certain states together because certain groups of state's claims are charged at different rates. Is there a way that i can reference these states all together in one formula rather than having to duplicate the formula over and over again and summing the results together?
Where i work, i constantly have to calculate revenue based off of claims. I run a report every month and i get many columns worth of data. This data is copied and pasted into its own tab in a workbook. I then reference that data with a COUNTIFS formula on another tab. this is the current formula i have constructed:
"=COUNTIFS(INDIRECT("'"&$E$2&" Claims'!A:A"),$A$26,INDIRECT("'"&$E$2&" Claims'!B:B"),$D$26,INDIRECT("'"&$E$2&" Claims'!D:D"),E27, INDIRECT("'"&$E$2&" Claims'!G:G"),I9)+COUNTIFS(INDIRECT("'"&$E$2&" Claims'!A:A"),$A$26,INDIRECT("'"&$E$2&" Claims'!B:B"),$D$26,INDIRECT("'"&$E$2&" Claims'!D:D"),E27, INDIRECT("'"&$E$2&" Claims'!G:G"),J9)+COUNTIFS(INDIRECT("'"&$E$2&" Claims'!A:A"),$A$26,INDIRECT("'"&$E$2&" Claims'!B:B"),$D$26,INDIRECT("'"&$E$2&" Claims'!D:D"),E27, INDIRECT("'"&$E$2&" Claims'!G:G"),K9)+COUNTIFS(INDIRECT("'"&$E$2&" Claims'!A:A"),$A$26,INDIRECT("'"&$E$2&" Claims'!B:B"),$D$26,INDIRECT("'"&$E$2&" Claims'!D:D"),E27, INDIRECT("'"&$E$2&" Claims'!G:G"),L9)"
In this formula, the tab im referencing every month is duplicated, and renamed according the current month and the new months data is imported monthly. thats why im using an INDIRECT function to reference the tab rather than a direct reference to one particular tab. cell $E$2 is where i input the month name and its joined with the word "Claims". in these tabs, Column A is where the Office name for every claim is listed, Column B is where the Line of the claim is listed (Workers Comp, Disability, etc). Column D is where the claim type is listed (Bodily Injury, Property Damage, etc). and column G:G is where the state of jurisdiction is located. This is where things get tricky. You can see in the formula that the cells I9, J9, K9, & L9 are referenced separately. ex: in the cell I9 is the letters CA. as you can see in this formula, for every state that i need to reference, i have to repeat the entire formula just to reference another additional State. Its important that i count certain states together because certain groups of state's claims are charged at different rates. Is there a way that i can reference these states all together in one formula rather than having to duplicate the formula over and over again and summing the results together?
