Need help simplifying a formula in sheets

[steelrz]

Hi. I?m new to this forum and Google sheets in general. I?ve created a formula which I feel is more complex than it needs to be, and I?m wondering if there?s a way to simplify it. Here is the formula and I?ll break down what I?m trying to obtain below:

=COUNTIFS(CW,"person",KOD,">=01/01/2022", KOD, "<=01/31/2022")+COUNTIFS(CW,"person",KOD,"WAVE",CKOD,">=01/01/2022", CKOD, "<=01/31/2022")+COUNTIFS(CW,"person",KOD,"offline",CKOD,">=01/01/2022", CKOD, "<=01/31/2022")+(COUNTIFS(CW,"person",KOD,"N/A",CKOD,">=01/01/2022", CKOD, "<=01/31/2022"))

• The person will be the same unique value throughout the whole formula. For example, John.
• The field KOD can contain a date, or various words such as wave, offline, or N/A. I already know that?s not ideal, but that?s what I have to work with on the spreadsheet.
• So I need to refer to two different columns to obtain a total value for one person. If column KOD contains a date, I need that item counted if it falls within a specified date range.
• If the column KOD contains one of the specified words, I need to refer to column CKOD to obtain a date, and then count that item if it falls within a specified range.

The formula is working as is, but it seems awfully long. Just wondering if there?s someway I can shorten it. Thank you so much for your help!

[janmorris]

It may be possible to shorted the formula, but without any data it will just be a guessing game.

And even though you say:

field KOD can contain a date, or various words such as wave, offline, or N/A. I already know that?s not ideal, but that?s what I have to work with on the spreadsheet.

I doubt that it MUST be that way... It sounds like the source data is not being handled efficiently.

Please follow the instructions in the yellow banner above your first post, to upload a sample file with data that accurately represents the variety you are saying exists in your file.