Max formula multiple IF statements

[John Gibbons]

Hello. I'm trying to combine multiple IF statements wrapped in a MAX formula based on data validation (MIN formula in second table below MAX table). The Analysis tab is where the formulas go, the Audits tab is a listing of all Audits, and the Holiday tab is as a lookup for the Audits tab. I have combined the IF statements wrapped in a MAX formula and have use the Ctrl & Shift + Enter. Analysis tab - When Cell C13 is "ALL", the calculation in Rows 32, 33, 39, and 40 work correctly. When the other 3 options are selected the results across the Rows are zero. Selection of "ALL" and or "ADT" in Cell C13 should give the same results as "ALL" in the first table. "ASM" results are correct if all Row 32 is zero. "DSK" results should be 0,16,0,0,13 in Cell C32 thru G32. The file is saved with "ALL" selected in Cell C13 on the Analysis Tab. Both tables are correctly calculating for "ALL". I just can't figure out why the formula doesn't work for the other 3 selections. Appreciate all feedback. Thank you.

 

[Nebu]

Hi:

Can't you use a pivot as attached? If you want only a formula solution use the native function "MAXIFS" in excel.

Note: The slicer will control both the pivot

Thanks

 

[John Gibbons]

Good evening. Thank you for the quick response. The MAXISFS is not available at home or work at this time. I believe your solution will work. I definitely like the idea of a pivot table with slicers than the extensive formulas. Thank you