Using "Right" formula within SumProduct

cjc155

Board Regular
Joined
Apr 21, 2005
Messages
70
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
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
just needs a slight tweak try the following

=SUMPRODUCT(--(A1:A100=C1),--(RIGHT(B1:B100,4)=RIGHT(D1,4)))
 
Upvote 0
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?
 
Upvote 0
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!!
 
Upvote 0
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)))
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,224,502
Messages
6,179,126
Members
452,890
Latest member
Nikhil Ramesh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top