IF(AND) returning FALSE value when it should be true

[Callaghan86]

Can someone help me with this formula please?

Essentially "Overview!B*" is the period, and "Overview!D*" is the date.

I know for a fact that the return value for the IF(AND) is TRUE, but when I evaluate it, it's saying it's FALSE.

=IF(AND(Overview!$D$11:$D$193=TODAY(),Overview!$B$11:$B$193=B10),SUMIFS(Overview!$K$11:$K$193,Overview!$B$11:$B$193,B10,Overview!$D$11:$D$193,"<"&TODAY()),SUMIFS(Overview!$K$11:$K$193,Overview!$B$11:$B$193,B10))

The first step of the evaluation is:

IF(AND(42662=42810). These 2 numbers obviously don't match.

The D column is a list of dates for an entire year. Why is it not finding today's value?

The SUMIFS can be disregarded, as I know these will work. It's the first step of the evaluation that is the problem.

 

[wideboydixon]

I guess D11 contains 19/10/2016. You can't check all values without using an array formula. It's not clear what you're trying to achieve.

WBD

 

[Callaghan86]

Basically our year is broken down into 13 periods. I want to say "If today falls within the period (B10), do one thing. If it doesn't, do another thing.

Why isn't it looking to find today's date matching with the period in B10? It normally works this way.

 

[wideboydixon]

Still not 100% clear but perhaps try this:

=IF(SUMPRODUCT((Overview!$D$11:$D$193=TODAY())*(Overview!$B$11:$B$193=B10))>0,SUMIFS(Overview!$K$11:$K$193,Overview!$B$11:$B$193,B10,   Overview!$D$11:$D$193,"<"&TODAY()),SUMIFS(Overview!$K$11:$K$193,Overview!$B$11:$B$193,B10))

WBD

 

[Callaghan86]

I've fixed the problem using the following:

=IF(AND(VLOOKUP(TODAY(),Overview!$D$5:$D$187,1,2),(VLOOKUP(B10,Overview!$B$5:$B$187,1,2))),SUMIFS(Overview!$K$5:$K$187,Overview!$B$5:$B$187,B10,Overview!$D$5:$D$187,"<"&TODAY()),SUMIFS(Overview!$K$5:$K$187,Overview!$B$5:$B$187,B10))

Thanks

 

[wideboydixon]

OK but I'm not 100% sure that your solution is correct because there's no guarantee that the two vlookups return data from the same row. I'll leave it with you.

WBD

 

[Callaghan86]

It seems to work for now! Just out of interest, what could I use instead of 2 separate VLOOKUPS? Sorry, I'm relatively new to Excel.

 

[Callaghan86]

just out of interest, would CONCATENATE be a simple solution? Concatenate the dates and period then search for the one value.

 

[wideboydixon]

I gave you a solution in post #4.

WBD