Spreadsheet Formulas

css navigation by Css3Menu.com

Compare 3 Lists Conditionally

Folks, we need to thank Chris in Australia for this one:

“I have been racking my brains for the last week trying to do a conditional count between two or three lists and this pointed me in the right direction.” Below is the modified formula.
{=SUM(IF(LEFT($B$7,4)=(LEFT('Sheet3'!$D$2:$D$500,4)),IF(LEFT($D$14,4)=(LEFT('Sheet3'!$E$2:$E$500,4)),IF($B17=('Sheet3'!$F$2:$F$500),1,0))))}
The modification is for three lists. The LEFT function mimics the use of wild cards, for example if you are wanting to query against (as in my case) the number of Helpdesk dockets that are problems and open for a particular system. Open dockets can be either Open, Open-Dispatch or Open-Rejected. The LEFT(A1, 4) function (On BOTH sides of the equation) only matches up the "Open" irrespective of any trailing text.


© 2003-2016

Updated:  11/16/2016 19:50
This page added:  19 May 2003