Spreadsheet Formulas

Compare 3 Lists Conditionally

“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-2008