Index Matching with SUMPRODUCT help

TEST BOOK 2.xlsx

Hi all,

I could use some help with a formula that's been giving me trouble.

I have a worksheet which has different batches of plants broken down by Batch ID, Total Plant Count & Square Footage per Plant.

The first five characters of each batch ID denote what room those plants came out of.

To find the square footage per plant, we divide 10,000 square feet by the total plants from each room.

On this sheet I have 8 different Batch IDs listed that came out of 2 different rooms, each room is 10,000 square feet.

I also left all my formulas I tried in there so you could see my work if that's helpful at all.

Thanks!!

Originally Posted by tanasedn

Hi,
try this:

Formula:

Please Login or Register  to view this content.

Thank you so much! It works great. I'm quite new to Excel so I'm still trying to fully understand the logic behind SUMPRODUCT. I wanted to also incorporate the RIGHT 6 characters needing to match as well. I tried this formula: _{=SUMPRODUCT(--(LEFT($A$2:$A$9,5)=LEFT(A6,5)),(RIGHT($A$2:$A$9,6)=RIGHT(A6,6)),$B$2:$B$9)}
But it returned a value of 0.