I would like to have my formula simplify. I am not great with adding and/or to my formulas.

Mary Kam

=COUNTIFS({COLLECT PLANT EXHANGE TEMPLATE ENTER STUDY}, [STUDY NUMBER]@row, {COLLECT PLANT EXHANGE TEMPLATE ROOM}, [ALL COPY]@row, {COLLECT PLANT EXHANGE TEMPLATE Range 5}, [REF DATE]@row, {COLLECT PLANT EXHANGE TEMPLATE TYPE 1}, [TYPE OF PLANT 3]@row) +

COUNTIFS({COLLECT PLANT EXHANGE TEMPLATE ENTER STUDY}, [STUDY NUMBER]@row, {COLLECT PLANT EXHANGE TEMPLATE ROOM}, [ALL COPY]@row, {COLLECT PLANT EXHANGE TEMPLATE Range 5}, [REF DATE]@row, {COLLECT PLANT EXHANGE TEMPLATE TYPE 2}, [TYPE OF PLANT 3]@row) +

COUNTIFS({COLLECT PLANT EXHANGE TEMPLATE ENTER STUDY}, [STUDY NUMBER]@row, {COLLECT PLANT EXHANGE TEMPLATE ROOM}, [ALL COPY]@row, {COLLECT PLANT EXHANGE TEMPLATE Range 5}, [REF DATE]@row, {COLLECT PLANT EXHANGE TEMPLATE TYPE 3}, [TYPE OF PLANT 3]@row) +

COUNTIFS({COLLECT PLANT EXHANGE TEMPLATE ENTER STUDY}, [STUDY NUMBER]@row, {COLLECT PLANT EXHANGE TEMPLATE ROOM}, [ALL COPY]@row, {COLLECT PLANT EXHANGE TEMPLATE Range 5}, [REF DATE]@row, {COLLECT PLANT EXHANGE TEMPLATE TYPE 4}, [TYPE OF PLANT 3]@row) +

COUNTIFS({COLLECT PLANT EXHANGE TEMPLATE ENTER STUDY}, [STUDY NUMBER]@row, {COLLECT PLANT EXHANGE TEMPLATE ROOM}, [ALL COPY]@row, {COLLECT PLANT EXHANGE TEMPLATE Range 5}, [REF DATE]@row, {COLLECT PLANT EXHANGE TEMPLATE TYPE 5}, [TYPE OF PLANT 3]@row) +

COUNTIFS({COLLECT PLANT EXHANGE TEMPLATE ENTER STUDY}, [STUDY NUMBER]@row, {COLLECT PLANT EXHANGE TEMPLATE ROOM}, [ALL COPY]@row, {COLLECT PLANT EXHANGE TEMPLATE Range 5}, [REF DATE]@row, {COLLECT PLANT EXHANGE TEMPLATE TYPE 6}, [TYPE OF PLANT 3]@row)

Genevieve P.

Hi @Mary Kam

If you want it all in one column, you're right, this is the way to go.

=IF(formula = 0, "", formula)

If you don't like how long this is, you could just have the first half in this current column. Then you could add in a second column to do the second half of the formula, check if it's 0 or not, like so:

=IF([Formula Column]@row = 0, "", [Formula Column]@row)

The only element I can think of that you could simplify is the beginning, where you're returning a blank cell based on two possible conditions. These can be in an OR statement, like so:

=IF(OR([STUDY NUMBER]@row = "", [Type of PLANT 2]@row = ""), "", 

The rest would need to be specifically identified, based on your set up.

Cheers,

Genevieve

Genevieve P.

Hi @Mary Kam

It looks like you have three columns that are the same in each COUNTIF, but then at the end you have 6 other columns to look in, is that correct?

So you're looking for the [STUDY NUMBER], [ALL COPY], and [REF DATE] to match across sheets.

Then if all three of those match, you're looking to count the [TYPE OF PLANT 3] but in Type 1 - 6 columns in the other sheet.

I presume that you wouldn't have duplicates (ex. "Type of Plant 3" appearing in the same row in both Column 1 and Column 2), is that correct?

The way you have it set up right now is exactly what I would do: 6 separate COUNTIFS statements added together. You have to think of it like

Type 1 column + Type 2 column + Type 3 column

But then you have other criteria with each formula, which is why it looks so long. If you want to help make it look more simple, you could always adjust the names of your cross-sheet references to be shorter (by clicking "edit reference" and adjusting the word used to identify that reference).

For example, this may be easier to read:

=COUNTIFS({ENTER STUDY}, [STUDY NUMBER]@row, {TEMPLATE ROOM}, [ALL COPY]@row, {Range 5}, [REF DATE]@row, {TYPE 1}, [TYPE OF PLANT 3]@row) +

COUNTIFS({ENTER STUDY}, [STUDY NUMBER]@row, {TEMPLATE ROOM}, [ALL COPY]@row, {Range 5}, [REF DATE]@row, {TYPE 2}, [TYPE OF PLANT 3]@row) +

COUNTIFS({ENTER STUDY}, [STUDY NUMBER]@row, {TEMPLATE ROOM}, [ALL COPY]@row, {Range 5}, [REF DATE]@row, {TYPE 3}, [TYPE OF PLANT 3]@row) +

COUNTIFS({ENTER STUDY}, [STUDY NUMBER]@row, {TEMPLATE ROOM}, [ALL COPY]@row, {Range 5}, [REF DATE]@row, {TYPE 4}, [TYPE OF PLANT 3]@row) +

COUNTIFS({ENTER STUDY}, [STUDY NUMBER]@row, {TEMPLATE ROOM}, [ALL COPY]@row, {Range 5}, [REF DATE]@row, {TYPE 5}, [TYPE OF PLANT 3]@row) +

COUNTIFS({ENTER STUDY}, [STUDY NUMBER]@row, {TEMPLATE ROOM}, [ALL COPY]@row, {Range 5}, [REF DATE]@row, {TYPE 6}, [TYPE OF PLANT 3]@row)

But it's the same formula structure. If it's working for you, keep it! 🙂

Cheers,

Genevieve

Mary Kam

I have to replicate this formula for 365 days of the year with a change of REF DATE so I wanted it simplified so I need to just change 1 cell instead of 6 cells.

Genevieve P.

Hi @Mary Kam

I'm not quite sure I know what you mean - it looks like you only reference the REF Date once in your formula, the same one for each of the COUNTIFS:

{Range 5}, [REF DATE]@row

This means you just need to update the [REF DATE]@row cell in your current sheet and it will update for all 6 of them at once.

Would you maybe be able to post a screen capture of your source sheet and your current metric sheet, explaining what you are counting? But please block out sensitive data!

Thanks,

Genevieve

Mary Kam

1 Ex 1 = TYPE OF PLANT, 1 EX TYPE 2 = TYPE OF PLANT 2, 1 EX TYPE 3 = TYPE OF PLANT 3

I need to keep track of how many types of plants I have per day in each study number. Everything works but I just wanted to simplify the formula. Do you think there is a simplier way to keep track of inventory?

Thanks

Genevieve P.

Hi @Mary Kam

I presume this is your destination sheet pictured above. This seems like a fairly complicated set-up, I agree...I would normally suggest creating a Grouped Report instead (grouping first by Date, then Study Number, then Type of Plant) but you have three columns for the Type of Plant so that won't work.

Based on your variables it looks like to get your end-goal this current formula is working for you. In this case I would keep it as-is!

Mary Kam

I wanted to avoid having 0 as a return value so the formula has doubled in size. If there is no other way I will leave it, but you think of something let me know. Thanks for your prompt reply.

=IF([STUDY NUMBER]@row = "", "", IF([Type of PLANT 2]@row = "", "", IF(COUNTIFS({COLLECT PLANT EXHANGE TEMPLATE ENTER STUDY}, [STUDY NUMBER]@row, {COLLECT PLANT EXHANGE TEMPLATE ROOM}, [ALL COPY]@row, {COLLECT PLANT EXHANGE TEMPLATE Range 5}, [REF DATE]@row, {COLLECT PLANT EXHANGE TEMPLATE TYPE 1}, [Type of PLANT 2]@row)

+ COUNTIFS({COLLECT PLANT EXHANGE TEMPLATE ENTER STUDY}, [STUDY NUMBER]@row, {COLLECT PLANT EXHANGE TEMPLATE ROOM}, [ALL COPY]@row, {COLLECT PLANT EXHANGE TEMPLATE Range 5}, [REF DATE]@row, {COLLECT PLANT EXHANGE TEMPLATE TYPE 2}, [Type of PLANT 2]@row)

+ COUNTIFS({COLLECT PLANT EXHANGE TEMPLATE ENTER STUDY}, [STUDY NUMBER]@row, {COLLECT PLANT EXHANGE TEMPLATE ROOM}, [ALL COPY]@row, {COLLECT PLANT EXHANGE TEMPLATE Range 5}, [REF DATE]@row, {COLLECT PLANT EXHANGE TEMPLATE TYPE 3}, [Type of PLANT 2]@row)

+ COUNTIFS({COLLECT PLANT EXHANGE TEMPLATE ENTER STUDY}, [STUDY NUMBER]@row, {COLLECT PLANT EXHANGE TEMPLATE ROOM}, [ALL COPY]@row, {COLLECT PLANT EXHANGE TEMPLATE Range 5}, [REF DATE]@row, {COLLECT PLANT EXHANGE TEMPLATE TYPE 4}, [Type of PLANT 2]@row)

+ COUNTIFS({COLLECT PLANT EXHANGE TEMPLATE ENTER STUDY}, [STUDY NUMBER]@row, {COLLECT PLANT EXHANGE TEMPLATE ROOM}, [ALL COPY]@row, {COLLECT PLANT EXHANGE TEMPLATE Range 5}, [REF DATE]@row, {COLLECT PLANT EXHANGE TEMPLATE TYPE 5}, [Type of PLANT 2]@row)

+ COUNTIFS({COLLECT PLANT EXHANGE TEMPLATE ENTER STUDY}, [STUDY NUMBER]@row, {COLLECT PLANT EXHANGE TEMPLATE ROOM}, [ALL COPY]@row, {COLLECT PLANT EXHANGE TEMPLATE Range 5}, [REF DATE]@row, {COLLECT PLANT EXHANGE TEMPLATE TYPE 6}, [Type of PLANT 2]@row) = "0", "",

COUNTIFS({COLLECT PLANT EXHANGE TEMPLATE ENTER STUDY}, [STUDY NUMBER]@row, {COLLECT PLANT EXHANGE TEMPLATE ROOM}, [ALL COPY]@row, {COLLECT PLANT EXHANGE TEMPLATE Range 5}, [REF DATE]@row, {COLLECT PLANT EXHANGE TEMPLATE TYPE 1}, [Type of PLANT 2]@row)

+ COUNTIFS({COLLECT PLANT EXHANGE TEMPLATE ENTER STUDY}, [STUDY NUMBER]@row, {COLLECT PLANT EXHANGE TEMPLATE ROOM}, [ALL COPY]@row, {COLLECT PLANT EXHANGE TEMPLATE Range 5}, [REF DATE]@row, {COLLECT PLANT EXHANGE TEMPLATE TYPE 2}, [Type of PLANT 2]@row)

+ COUNTIFS({COLLECT PLANT EXHANGE TEMPLATE ENTER STUDY}, [STUDY NUMBER]@row, {COLLECT PLANT EXHANGE TEMPLATE ROOM}, [ALL COPY]@row, {COLLECT PLANT EXHANGE TEMPLATE Range 5}, [REF DATE]@row, {COLLECT PLANT EXHANGE TEMPLATE TYPE 3}, [Type of PLANT 2]@row)

+ COUNTIFS({COLLECT PLANT EXHANGE TEMPLATE ENTER STUDY}, [STUDY NUMBER]@row, {COLLECT PLANT EXHANGE TEMPLATE ROOM}, [ALL COPY]@row, {COLLECT PLANT EXHANGE TEMPLATE Range 5}, [REF DATE]@row, {COLLECT PLANT EXHANGE TEMPLATE TYPE 4}, [Type of PLANT 2]@row)

+ COUNTIFS({COLLECT PLANT EXHANGE TEMPLATE ENTER STUDY}, [STUDY NUMBER]@row, {COLLECT PLANT EXHANGE TEMPLATE ROOM}, [ALL COPY]@row, {COLLECT PLANT EXHANGE TEMPLATE Range 5}, [REF DATE]@row, {COLLECT PLANT EXHANGE TEMPLATE TYPE 5}, [Type of PLANT 2]@row)

+ COUNTIFS({COLLECT PLANT EXHANGE TEMPLATE ENTER STUDY}, [STUDY NUMBER]@row, {COLLECT PLANT EXHANGE TEMPLATE ROOM}, [ALL COPY]@row, {COLLECT PLANT EXHANGE TEMPLATE Range 5}, [REF DATE]@row, {COLLECT PLANT EXHANGE TEMPLATE TYPE 6}, [Type of PLANT 2]@row))))

Genevieve P.

Hi @Mary Kam

If you want it all in one column, you're right, this is the way to go.

=IF(formula = 0, "", formula)

If you don't like how long this is, you could just have the first half in this current column. Then you could add in a second column to do the second half of the formula, check if it's 0 or not, like so:

=IF([Formula Column]@row = 0, "", [Formula Column]@row)

The only element I can think of that you could simplify is the beginning, where you're returning a blank cell based on two possible conditions. These can be in an OR statement, like so:

=IF(OR([STUDY NUMBER]@row = "", [Type of PLANT 2]@row = ""), "", 

The rest would need to be specifically identified, based on your set up.

Cheers,

Genevieve