Posts by Gemma86

thank you

• Separate Letters and Numbers Formula

Hi,

Spreadsheet attached but looking for formula to populate B and C

Thanks

• formula - if cell contain specific text then return all values in the list

i used this

1. =IFERROR(INDEX(\$A\$1:\$A\$11,SMALL(INDEX((\$B\$1:\$B\$11="Fruit")*(ROW(\$B\$1:\$B\$11)-MIN(ROW(\$B\$1:\$B\$11))+1),0),COUNTIF(\$B\$1:\$B\$11,"<>"&"Fruit")+ROW(\$A1))),"-")
• formula - if cell contain specific text then return all values in the list

I need to amend this slightly to only bring back the data if there is data in column c.

THANK YOU

• formula - if cell contain specific text then return all values in the list

I would like a forumla to populate G2, G3 and G4, using the data from A and B

Thanks

• If blank bring back nothing

Re: If blank bring back nothing

Thank you for your quick response, but I have tried that already and it doesn't remove it

• If blank bring back nothing

hi how can i amend the below to bring back a blank if the answer is 0?

=COUNTIF(C7:C32,3)/C33

Thanks

• vlookup to count based on criteria?

Re: vlookup to count based on criteria?

Can anyone think of a good way of analysing if linked products are sold in orders?

• If date and time formatted cell is 2 hours after different date and time formatted

Re: If date and time formatted cell is 2 hours after different date and time formatte

Perfect works a treat!

but i have just noticed, sometimes, the cell doesnt have a date and time in it, sometimes it is blank or has text in it, when this happens i need it to pull back a 0

• If date and time formatted cell is 2 hours after different date and time formatted

I'm trying to write a formula that says,

if cell containing at date and time in the format of 10/02/2017 13:38 is 2 hours after another cell with a different date and time in the format of 10/02/2017 14:45 then display a 1 otherwise display 0

Any ideas?

Also once I have done this, id like the be able to sum up all the 1s. For some reason sum and count is being back 0 in all instances.

• if and multiple criteria and if cell doesnt not contain specfic word

Please can i have some help to amend the below

works up until the bit highlighted in blue

IF(AND(L2="Enabled")*AND(G2="Yes")*AND(U2="True")*AND(IF,NOT(ISERROR(SEARCH("Guarantee",E2)),"Guarantee Missing","")

Just to note, cell E2 will contain more than just guarantee.

Thank you!

• vlookup to count based on criteria?

Re: vlookup to count based on criteria?

It doesn't calculate correctly.

For order [TABLE="width: 87"]

[tr]

[TD="class: xl63, width: 87"]O0674578[/TD]

[/tr]

[/TABLE]
column P should be 0 and Q should be blank because no linked skus were purchased.

Also there are no calculations in column O

Also for sku 31193, cell N4 should be 0.

For order O0665619 P5 should be 1 and Q5 should be 10027

Thanks

• vlookup to count based on criteria?

Re: vlookup to count based on criteria?

forum.ozgrid.com/index.php?attachment/71670/

Sorry, really struggling to get my head around the formulas,

is it possible someone could amend the attachment? None of the forumlas are working correctly for me.

• vlookup to count based on criteria?

Re: vlookup to count based on criteria?

Could this be done in a pivot table?

• vlookup to count based on criteria?

I am trying to report on if customers buy batteries (linked products) when they buy a product that required batteries.

I am trying to find forumla to populate columns D - F

• Divide a number by a different percentage depending on value

Re: diivde a number by a different percentage depending on value

done it now thanks anyway

• Divide a number by a different percentage depending on value

Re: diivde a number by a different percentage depending on value

it doesnt work.

the formula doesnt mention cell "I2"

i have attached the spreadsheet to see if that help.

• Divide a number by a different percentage depending on value

How do I write the below formula:

If j2 is >=3400 then divide I2 by 1%, if j2 is >=3200 and <3400 then divide I2 by 0.85%, if j2 is >=2800 <3200 then divide I2 by 0.65%, if j2 is >=2300 and <2800 then divide I2 by 0.50%, if <=2300 then divide by 0.40%

So in this example the answer is 1488.79

• percentage difference and if error leave blank

Re: percentage difference and if error leave blank

amazing!