Printing a result when two criteas are met on a row in excel 1

[jupops]

Good Morning all

Can anybody please help?

I am trying to create a spreadsheet that will print a cell when two other cells are set to true. So the first condition is – if 41 is in column B, the next condition is “A” is on the same row on Column D, then print out the cells detail on the same row in column F.

For example

41 41 0 A 310 14
0 41 0 B 45 4
0 41 0 C 99 0

Therefore the cell printed should give me – 14.

I have tried an if formula:

=IF((B:B=41)*AND(D="A"),F:F,0) – (which give me a result 0)

I have tried to amend this formula into an Arrey formula, butb get the same resukt, can anyinne suggest where I am going wrong, thank you.

Regards
Jupops

 

[Gavona]

Try either sumproduct or
=AND(condition1, condition2)
This will give True or 1 on any row that you want to print.

I don't really see exactly what you are trying to print out. You could:
1) filter on the results of that formula or
2)or you could put the formula into conditional formatting criteria so that when not true the text is the same colour as the background or
3) you could incorporate it into an IF statement as in your original



Gavin

 

[jupops]

Thank you for your help, I used the sumproduct which worked, see below.

=SUMPRODUCT((Auto!B9:B1500='Translation Sheet'!B2)*(RIGHT(Auto!C91500,1)="A")*Auto!H9:H1500)

Cheers