I can't seem to make my IF/AND formula work

Iznil76

I want to show a figure of 48000 in a cell when the Total Biogas Actual m3 cell on the row is greater than 0 and the LG cell on the row states Local Generation:

=IF(AND([Total Biogas Actual m3]1= >0, [LG]1= Local Generation), "48000", "0")

I've tried several different ways but all I get is #UNPARSEABLE or #INCORRECTARGUMENT

Can anyone help please?

thanks

Linzi :-)

John_Foster

Hi @Iznil76,

Try this and see if it works for you.

=IF(AND([Total Biogas Actual m3]1= >0, [LG]1= "Local Generation"), 48000, 0)

I have put Lead Generation within quotes and taken the numerical values out of the quotes. You will need to make sure that the field this formula is in is a Text/Number field.

Thanks,

John

Iznil76

Hi John,

Thanks for responding. I copied and pasted your formula but unfortunately it shows as an #INVALID OPERATION.

The field is a Text/Number field so not really sure why this isn't working :-(

Thanks

Linzi

John_Foster

@Iznil76,

I have just set up a sheet to test this and the formula is not something I have seen before =, but it has now worked for me.

=IF(AND([\[Total Biogas Actual m3]1 >= 0, LG1 = "Local Generation"), 48000, 0)

Fingers crossed!

John

Iznil76

@John_Foster

Thanks so much again for coming back to me. Unfortunately, this returns a #UNPARSEABLE.

I'm thinking I may have to go the long way around and split my data out into different sheets and use VLOOKUP.

It's a conundrum I knew I would come up against one day!

many thanks

Linzi

John_Foster

Hi @Iznil76,

You should not need too, I would be happy to look at yours as long as there is no sensitive data in it that you wouldn't want me to see.

Alternatively I have added a link below to the sheet I tried it in, feel free to have a look.

https://app.smartsheet.com/b/publish?EQBCT=466267e4385144ce9ebdcb0102811257

Thanks,

John