If/And formula

**ddalferes** · 12-19-2011, 03:16 PM

I have attached my whole spreadsheet and now you will see I am a true rookie at this. What I am trying to do is fill in this summary sheet with the data on the first spreadsheet. I think it is because of the dates, that I can't get it to work

**arthurbr** · 12-19-2011, 03:23 PM

Hi and welcome to the board

Please post a sample sheet - Data formatting might be important

**scottylad2** · 12-19-2011, 03:48 PM

All your expiry dates are in 2011, what years data would be summed for that?

**ddalferes** · 12-19-2011, 05:00 PM

I don't have any 2011 expiry dates

**ddalferes** · 12-21-2011, 11:26 AM

I have attached my whole spreadsheet and now you will see I am a true rookie at this. What I am trying to do is fill in this summary sheet with the data on the first spreadsheet. I think it is because of the dates, that I can't get it to work

**Haseeb Avarakkan** · 12-21-2011, 02:29 PM

Try this in Summary B4,

=SUM(IF(TEXT(DATE(YEAR(Expirations!$C$1:$D$301)+3,MONTH(Expirations!$C$1:$D$301)+1,0),"yyyymmm")=$A4&B$3,--LEFT(Expirations!$B$1:$B$301,FIND(" ",Expirations!$B$1:$B$301&" ")-1)))

This is an Array Formula (must hit CTRL+SHIFT+ENTER, rather than just ENTER then copy down & across.

**ddalferes** · 12-21-2011, 06:28 PM

Originally Posted by Haseeb A

Try this in Summary B4,

=SUM(IF(TEXT(DATE(YEAR(Expirations!$C$1:$D$301)+3,MONTH(Expirations!$C$1:$D$301)+1,0),"yyyymmm")=$A4&B$3,--LEFT(Expirations!$B$1:$B$301,FIND(" ",Expirations!$B$1:$B$301&" ")-1)))

This is an Array Formula (must hit CTRL+SHIFT+ENTER, rather than just ENTER then copy down & across.

Boy I'm way in over my head! I typed the formula and am getting all 0 except in December 2015, which is my largest group, I am getting the result of 32, which is only one of the cells to be added. Since I don't know what I am doing I don't know what to check. Thanks anyway, I'll keep trying. Any thoughts?

**Haseeb Avarakkan** · 12-21-2011, 06:51 PM

Sorry my mistake. There was some error. The range should be Expirations!$C$1:$C$301 NOT Expirations!$C$1:$D$301

If Col_C are valid dates, then can avoid the helper columns.

Try the attached.