SOLVED

Adjusting a CountIF formula to survive a sort and filter

%3CLINGO-SUB%20id%3D%22lingo-sub-1567460%22%20slang%3D%22en-US%22%3EAdjusting%20a%20CountIF%20formula%20to%20survive%20a%20sort%20and%20filter%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1567460%22%20slang%3D%22en-US%22%3E%3CP%3EHello!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20currently%20completing%20a%20business%20information%20systems%20unit%20and%20I've%20encountered%20an%20issue%20where%20the%20task%20requires%20me%20to%20use%20a%20CountIF%20formula%20drawing%20details%20from%20a%20Milk%20Products%20table%20(I7%3AQ18)%20and%20adjusting%20it%20with%20a%20ratio%20from%20the%20Milk%20production%20table%20(I28%3AN39).%20I%20would%20like%20the%20ability%20to%20be%20able%20to%20sort%20the%20Milk%20Products%20table%2C%20but%20my%20formula%20currently%20addresses%20fixed%20cell%20variables%2C%20and%20when%20I%20sort%20that%20table%2C%20the%20results%20understandably%20change.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20primary%20requirement%20for%20this%20particular%20column%20M31%3A38%20requires%20the%20use%20of%20a%20CountIf%20formula%2C%20but%20I%20can't%20figure%20out%20what%20to%20pair%20with%20it%20to%20have%20it%20survive%20a%20re-sort%20of%20I7%3AQ18.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20attached%20the%20spreadsheet%20for%20ease%20of%20reference.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20would%20be%20greatly%20appreciated!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETIA%2C%3C%2FP%3E%3CP%3EPaul%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1567460%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1567486%22%20slang%3D%22en-US%22%3ERe%3A%20Adjusting%20a%20CountIF%20formula%20to%20survive%20a%20sort%20and%20filter%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1567486%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F750280%22%20target%3D%22_blank%22%3E%40PaulOlsen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DCOUNTIF(%24I%249%3A%24I%2416%2CI31)*%0A%20%20INDEX(%24K%249%3A%24K%2416%2CMATCH(%24I31%2C%24I%249%3A%24I%2416%2C0))*%0A%20%20INDEX(%24L%249%3A%24L%2416%2CMATCH(%24I31%2C%24I%249%3A%24I%2416%2C0))*%0A%20%20INDEX(%24N%249%3A%24N%2416%2CMATCH(%24I31%2C%24I%249%3A%24I%2416%2C0))*%0A%20%20K31%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1567577%22%20slang%3D%22en-US%22%3ERe%3A%20Adjusting%20a%20CountIF%20formula%20to%20survive%20a%20sort%20and%20filter%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1567577%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWow.%20That's%20incredibly%20impressive%20and%20simpler%20than%20I%20was%20trying%20to%20make%20it.%20Thanks%20so%20much%20Sergei%2C%20I've%20been%20throwing%20my%20head%20at%20this%20for%20a%20week%20now%20trying%20to%20make%20it%20work!%20Am%20I%20right%20in%20reading%20this%20formula%20that%20its%20sorting%20the%20calculations%20for%20this%20by%20indexing%20against%20each%20of%20the%20columns%20which%20are%20filtered%2C%20matching%20it%20against%20the%20product%20code%20criteria%20in%20milk%20production%2C%20lining%20it%20up%20with%20the%20equivalent%20criteria%20in%20milk%20products%2C%20and%20then%20selecting%20the%20exact%20match%20based%20on%20that%20information%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJust%20want%20to%20make%20sure%20I%20understand%20how%20its%20working%2C%20instead%20of%20copying%20it%20and%20calling%20it%20a%20day.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20SO%20much%20for%20your%20time%2C%20you've%20just%20cured%20a%20very%20big%20perpetual%20headache.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EKind%20Regards%2C%3C%2FP%3E%3CP%3EPaul%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1567755%22%20slang%3D%22en-US%22%3ERe%3A%20Adjusting%20a%20CountIF%20formula%20to%20survive%20a%20sort%20and%20filter%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1567755%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F750280%22%20target%3D%22_blank%22%3E%40PaulOlsen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPaul%2C%20you%20are%20welcome.%3C%2FP%3E%0A%3CP%3EYes%2C%20MATCH%20finds%20the%20current%20row%20number%20for%20the%20product%20code%20in%20%22Milk%20Products%22%20table%2C%20and%20INDEX%20takes%20value%20from%20another%20column%20for%20the%20found%20row.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello!

 

I'm currently completing a business information systems unit and I've encountered an issue where the task requires me to use a CountIF formula drawing details from a Milk Products table (I7:Q18) and adjusting it with a ratio from the Milk production table (I28:N39). I would like the ability to be able to sort the Milk Products table, but my formula currently addresses fixed cell variables, and when I sort that table, the results understandably change.

 

The primary requirement for this particular column M31:38 requires the use of a CountIf formula, but I can't figure out what to pair with it to have it survive a re-sort of I7:Q18.

 

I've attached the spreadsheet for ease of reference.

 

Any help would be greatly appreciated!

 

TIA,

Paul

3 Replies
Best Response confirmed by PaulOlsen (New Contributor)
Solution

@PaulOlsen 

That could be

=COUNTIF($I$9:$I$16,I31)*
  INDEX($K$9:$K$16,MATCH($I31,$I$9:$I$16,0))*
  INDEX($L$9:$L$16,MATCH($I31,$I$9:$I$16,0))*
  INDEX($N$9:$N$16,MATCH($I31,$I$9:$I$16,0))*
  K31

@Sergei Baklan 

Wow. That's incredibly impressive and simpler than I was trying to make it. Thanks so much Sergei, I've been throwing my head at this for a week now trying to make it work! Am I right in reading this formula that its sorting the calculations for this by indexing against each of the columns which are filtered, matching it against the product code criteria in milk production, lining it up with the equivalent criteria in milk products, and then selecting the exact match based on that information?

 

Just want to make sure I understand how its working, instead of copying it and calling it a day.

 

Thanks SO much for your time, you've just cured a very big perpetual headache.

 

Kind Regards,

Paul

@PaulOlsen 

Paul, you are welcome.

Yes, MATCH finds the current row number for the product code in "Milk Products" table, and INDEX takes value from another column for the found row.