Using "Right" formula within SumProduct

[cjc155]

Hi all,

In my spreadsheet I have two columns. The possible values within Column A are size (SMall, Medium LArge). The possible values in Column B are Season/Year references (Spring 2011, Summer 2011, Fall 2011, Winter 2011, Summer 2012...)

What I am trying to do is create a sumproduct formula that says, count number of Small items that are in place within the YEAR of 2011. C1 is cell reference for Small and D1 is a cell which currently contains Spring 2011.

I have tried

=sumproduct(--(A1:A100="C1"),--(B1:B100=right(D1,4)))

I have also tried replacing the back half of the equation with (isnumber(find(right(D1,4)...

but that hasn't worked either. Any help would be much appreciated.

Thanks

 

[jimrward]

just needs a slight tweak try the following

=SUMPRODUCT(--(A1:A100=C1),--(RIGHT(B1:B100,4)=RIGHT(D1,4)))

 

[GTO]

Firstly, ditch the quotes around C1. If D1 has "Spring 2011" in it, and the values in Col B are "Spring 2011", "Spring 2012" etc, did you try comparing the entire cell value?

 

[cjc155]

Thanks so much. It works great when I have data and summary information in one sheet, but with my data on say "sheet1" and summary on Sheet2.

What needs to be in places so this works across sheets?

Again, thanks for the help!!

 

[T. Valko]

just needs a slight tweak try the following

=SUMPRODUCT(--(A1:A100=C1),--(RIGHT(B1:B100,4)=RIGHT(D1,4)))

Thanks so much. It works great when I have data and summary information in one sheet, but with my data on say "sheet1" and summary on Sheet2.

What needs to be in places so this works across sheets?

Again, thanks for the help!!

Just add the sheet name...

=SUMPRODUCT(--(Sheet1!A1:A100=C1),--(RIGHT(Sheet1!B1:B100,4)=RIGHT(D1,4)))

 

[cjc155]

ha, sorry. I meant I did that. It seems to be working with my example but in my actual file something isn't working. The code is returning a 0. In my actual file column A&B is a Data Validation list. Had some bad data causing it to break.

Many thanks to both of you!