Combining two formula (for tidyness)

[Stoobydoo]

Hi,

I've this formula:

G1 =IF(COUNTIF(A2:B2,">=1"),F1,"")

F1 =MIN(IF(('17 05 17 D'!$A$2:$A$50000=A132)*('17 05 17 D'!$I$2:$I$50000=AV132),IF('17 05 17 D'!$D$2:$D$50000="",FALSE,'17 05 17 D'!$D$2:$D$50000)))

I'd like to amalgamate F1 into G1

So if Count of A2 to B2 is greater or equal to 1 then above formula, if not then blank.

last help for the day, I hope.. I'm under a little pressure to complete this.

[63falcondude]

Is this what you are looking for?

=IF(COUNTIF(A2:B2,">=1"),MIN(IF(('17 05 17 D'!$A$2:$A$50000=A132)*('17 05 17 D'!$I$2:$I$50000=AV132),IF('17 05 17 D'!$D$2:$D$50000="",FALSE,'17 05 17 D'!$D$2:$D$50000))),"")
Ctrl Shift Enter

[Stoobydoo]

Hi,

It's likely me but now it's not working.

On it's own the long array formula works but where it doesn't find values it returns a 0. I was correcting this by adding another formula that if the cell was 0 then show a blank else show the result.

It would only be a 0 if cells A2 and B2 were both empty hence my thoughts on the countif.

Now (likely my fault) the formula suggestion you've provided deals with adding a blank where A2:B2 are both empty but it now adds a N/A everywhere else.

Thoughts? can I provide more info?

Note: here is the formula you provided, I've changed it (I should have provided this direction at the start) to my first row.

=IF(COUNTIF(AO3:AP3,">=1"),MIN(IF(('17 05 17 D'!$A$2:$A$50000=A3)*('17 05 17 D'!$I$3:$I$50000=AV3),IF('17 05 17 D'!$D$3:$D$50000="",FALSE,'17 05 17 D'!$D$3:$D$50000))),"")

[63falcondude]

All that I did was substitute F1 (in the G1 formula) with the formula that was in F1.

I cannot troubleshoot a non-working formula based on the formula alone. If you upload a sample of your sheet, we should be able to help you further.

[Stoobydoo]

Ok falcondude, yes what you've said makes complete sense as it might be a complete start from scratch. I've created a simplified spread sheet to reduce 'columns' (it's attached I think), it'll still do what I want.

In column E2 as an example I would like it to check if there is 1 or more value in B2 or C2, if there is I would like it to look for examples of A2 in in Column A of the Data tab and where there are also matches of D2 return the min date from the column B of the data sheet.

I've typed in what the results should be, E3 and B3 should come back blank.

thanks!

[63falcondude]

I've typed in what the results should be, E3 and B3 should come back blank.

Where? I don't see them.

What should the values in E2 and F2 be?

[Stoobydoo]

Sorry:

Results sheet - E2 -
I'd like this to recognise the asset in results sheet A2, also recognise the float in results sheet D2, I'd like it to then look for matches across rows in the Data Tab column A and column D, where it finds matches I'd like it to return the earliest start from the Data Tab column B.

I'd like E3, and E4 to do the same as E2. Where it would find no matches in E3 I'd like the formula to return blank.

Results sheet F2
I'd like it to do the same as the above except return the max date from Data Tab column C

I'd like F3, and F4 to do the same as F2. Where it would find no matches for F3 I'd like the formula to return blank.

Make sense?

[Stoobydoo]

Revisited this with a fresh mind post replying:

for E2:

=MIN(IF((Data!$A$2:$A$18=Result!A2)*(Data!$D$2:$D$18=Result!D2),IF(Data!$B$2:$B$18="",FALSE,Data!$B$2:$B$18)))

The formula above seems to work BUT

I wanted to add a fail safe to it, again, saying if in the results tab there is no value in B2, or C2 then to not run the formula but instead return blank.