Can someone please help me with a COUNTIFS formula?

michelle.farsund-hall91806

I can't seem to get this formula to calculate any results other than 0

COUNTIFS({11.08.2024- Labor Allocations for Smartshe ES}, [Expense Specialist]@row, {11.08.2024- Labor Allocations for Smartshe FU}, OR(@cell = "410101", @cell = "41201", @cell = "410202", @cell = "410203".

This is only a part of a larger formula.

kira11

Got it, try this:

=COUNTIFS({11.08.2024- Labor Allocations for Smartshe ES}, [Expense Specialist]@row, {11.08.2024- Labor Allocations for Smartshe Excep}, 0, {11.08.2024- Labor Allocations for Smartshe FU}, OR(@cell = 410201, @cell = 410101, @cell = 410202, @cell = 410203), {11.08.2024- Labor Allocations for Smartshe Titl}, OR(CONTAINS("SP", @cell), CONTAINS("SPQREA", @cell), CONTAINS("LP", @cell))

kira11

If your data type is just text/number for the FU cells, maybe try taking the quotes out around the numbers and make sure you have the correct closing parentheses.

michelle.farsund-hall91806

Ok thanks, removing the "" worked! However when I try combining it with other criteria now its not working.

Says, #UNPARSEABLE now- assuming I have a missing ? or something?

=COUNTIFS({11.08.2024- Labor Allocations for Smartshe ES}, [Expense Specialist]@row, {11.08.2024- Labor Allocations for Smartshe Excep}, 0, 11.08.2024- Labor Allocations for Smartshe ES}, [Expense Specialist]@row, {11.08.2024- Labor Allocations for Smartshe FU}, OR(@cell = 410201, @cell = 410101, @cell = 410202, @cell = 410203 ))

kira11

It looks like it maybe be a duplicate and you don't need, but it looks like a missing curly bracket after the "0,".

michelle.farsund-hall91806

Now I've added one more criteria and get UNPARSEABLE

=COUNTIFS({11.08.2024- Labor Allocations for Smartshe ES}, [Expense Specialist]@row, {11.08.2024- Labor Allocations for Smartshe FU}, OR(@cell = 410201, @cell = 410101, @cell = 410202, @cell = 410203, {11.08.2024- Labor Allocations for Smartshe Titl}, OR (@cell = SP, @cell = SPQREA, @cell = LP )))

michelle.farsund-hall91806

Ok this formula works

=COUNTIFS({11.08.2024- Labor Allocations for Smartshe ES}, [Expense Specialist]@row, {11.08.2024- Labor Allocations for Smartshe Excep}, 0, {11.08.2024- Labor Allocations for Smartshe FU}, OR(@cell = 410201, @cell = 410101, @cell = 410202, @cell = 410203 ))

But now I need to add this piece to it

{11.08.2024- Labor Allocations for Smartshe Titl}, OR(CONTAINS("SP", @cell), ("SPQREA", @cell), ("LP", @cell))))

kira11

For the text OR statement you do need the quotation marks and the closing parentheses is missing. Try this:

=COUNTIFS({11.08.2024- Labor Allocations for Smartshe ES}, [Expense Specialist]@row, {11.08.2024- Labor Allocations for Smartshe FU}, OR(@cell = 410201, @cell = 410101, @cell = 410202, @cell = 410203), {11.08.2024- Labor Allocations for Smartshe Titl}, OR (@cell = "SP", @cell = "SPQREA", @cell = "LP"))

michelle.farsund-hall91806

Still #UNPARSEABLE

michelle.farsund-hall91806

So the last piece just need to contain not=

Something like the below:

=COUNTIFS({11.08.2024- Labor Allocations for Smartshe ES}, [Expense Specialist]@row, {11.08.2024- Labor Allocations for Smartshe Excep}, 0, {11.08.2024- Labor Allocations for Smartshe FU}, OR(@cell = 410201, @cell = 410101, @cell = 410202, @cell = 410203)), {11.08.2024- Labor Allocations for Smartshe Titl}, OR(CONTAINS("SP", @cell), ("SPQREA", @cell), ("LP", @cell)))

kira11

Got it, try this:

=COUNTIFS({11.08.2024- Labor Allocations for Smartshe ES}, [Expense Specialist]@row, {11.08.2024- Labor Allocations for Smartshe Excep}, 0, {11.08.2024- Labor Allocations for Smartshe FU}, OR(@cell = 410201, @cell = 410101, @cell = 410202, @cell = 410203), {11.08.2024- Labor Allocations for Smartshe Titl}, OR(CONTAINS("SP", @cell), CONTAINS("SPQREA", @cell), CONTAINS("LP", @cell))

michelle.farsund-hall91806

Funny, I just tried this at the same time you posted this and it works :)

=COUNTIFS({11.08.2024- Labor Allocations for Smartshe ES}, [Expense Specialist]@row, {11.08.2024- Labor Allocations for Smartshe Excep}, 0, {11.08.2024- Labor Allocations for Smartshe FU}, OR(@cell = 410201, @cell = 410101, @cell = 410202, @cell = 410203 ), {11.08.2024- Labor Allocations for Smartshe Titl}, OR(CONTAINS("SP", @cell), CONTAINS("SPQREA", @cell), CONTAINS("LP", @cell)))

michelle.farsund-hall91806

Thank you so much for your help, really appreciate it!!!!

kira11

No problem! Glad it worked!

michelle.farsund-hall91806

OK another one for you, if you can still help me.

I get UNPARSEABLE

=COUNTIFS({11.08.2024- Labor Allocations for Smartshe ES}, [Expense Specialist]@row, {11.08.2024- Labor Allocations for Smartshe Excep}, 0, {11.08.2024- Labor Allocations for Smartshe FU}, OR(@cell = 410201, @cell = 410101, @cell = 410202, @cell = 410203 ), {11.08.2024- Labor Allocations for Smartshe Titl}, OR(CONTAINS("Managing", @cell), CONTAINS("Ops", @cell), {11.08.2024- Labor Allocations for Smartshe Categ}, OR(@cell <> AD, @cell <> AP )))

michelle.farsund-hall91806

Seems to work up until the last piece where the Category can't = AD or AP