Forum Discussion
COUNTIF from other tab
I have the above formula for a complaints risk assessment. I've linked it to our complaints tab so that if a item has a had a previous compliant against it, when I cut and paste a production schedule it flags based on the above formula. The complaints tab has complaints type listed as well on the same row as the item code. Is there any way to link this / make this come up too? thanks
2 Replies
ehcabman86 I'm not exactly sure what you're asking. Are you wanting to use multiple criteria in the formula, where the value in cell A2 matches the value in range 'complaints FY23-24'!I30:I52, AND the "Type" value in cell P2 matches the value in another column? If so, which column on the 'complaints FY23-24' worksheet contains the "Types"? The COUNTIFS function can handle multiple criteria. For example:
=IF(COUNTIFS('complaints FY23-24'!$I$30:$I$52, A2, 'complaints FY23-24'!$K$30:$K$52, P2), "Yes", "No")Note: I've used range 'complaints FY23-24'!K30:K52 to demonstrate the formula. Adjust as necessary to match your data. Also, the ">0" part is not needed, because the IF function will treat any number other than zero as TRUE.
If this is not what you meant, please provide additional context to better explain the question. Cheers!
- Hi thanks for answering! Thanks, didn't realise the >0 wasn't needed. Yes so the complaint type is on the other tab (complaints), column G I think. All I was after was to flag up if column Q (high risk) matches the same item code from the range from the complaints tab AND have column P (type) replicate the complaint type too. So I think it is multiple crieria i.e so if Q flags then column P would be give the complaint type. (there are about 5 types)
