How can I count items only when 3 specific criteria are met?

PKane

Hello All,

Novice user here -

I am building a data sheet for metrics and am hoping someone could please help me figure out the best way to create a COUNTIF/IFS formula that will only count a status 'type' (Complete, Scheduled, etc.) if the Core OpCo is 'FLA' and the year is 2022 and the LFCM OT box is 'checked/True'?

I'm not sure where to begin, or if using COUNTIF/IFS is the correct way to go about it?

Kleerfyre

COUNTIFS would be your best place to start. It would look Something like this:

=COUNTIFS([DC UPS Status]:[DC UPS Status], "Complete", [Core OpCo]:[Core OpCo], "FLA", [DC UPS Year]:[DC UPS Year], "2022", [LFCM OT DC UPS Scope?]:[LFCM OT DC UPS Scope?], 1)

See if that returns what you are looking for.

PKane

Hello Kleerfyre,

I REALLY appreciate your help!

I tried using the formula, but I didn't get the results I needed unfortunately. It returns #unparseable. I need to have it count these status types from a column in another sheet and pull it into my metrics sheet - that is one part that I neglected to mention. Is it possible to use a COUNTIFS in conjunction with a VLOOKUP?

Or maybe there is a better way to go about it?

Genevieve P.

Hi @PKane

You can turn a COUNTIFS formula from referencing columns in-sheet to use cross-sheet references! For example:

=COUNTIFS({DC UPS Status column}, "Complete", {Core OpCo column}, "FLA", {DC UPS Year column}, "2022", {LFCM OT DC UPS Scope column}, 1)

Here are some links that may help you:

COUNTIFS Function / Create cross sheet references to work with data in another sheet

However before changing up the references we should sort out why you're getting an error with Jonathan's formula. Can you post a screen capture of what you tried in the cell? We'll need to double check that all the column names are spelled properly and that the formula syntax is correct.

Cheers!

Genevieve