Pineapple_Crazy
Board Regular
- Joined
- May 2, 2017
- Messages
- 51
Hello,
I'm trying to create a formula using SUMPRODUCT that takes into account multiple criteria to return a sum. A portion of the formula is found below for a MTD calculation. However, I can have multiple occurrences of text that are the exact same. I want to be able to return the nth value found at "B7" in the formula below. Like 1st occurrence, 2nd occurrence, 3rd occurrence, etc.
Anyone have any suggestions here?
Thank you!
=IF($B$3="MTD",SUMPRODUCT((TMH!$A$3:$A$100=B7)*(MONTH(TMH!$B$1:$M$1)=MONTH($B$2))*(YEAR($B$2)=YEAR($B$2))*(TMH!$B$3:$M$100)))
I'm trying to create a formula using SUMPRODUCT that takes into account multiple criteria to return a sum. A portion of the formula is found below for a MTD calculation. However, I can have multiple occurrences of text that are the exact same. I want to be able to return the nth value found at "B7" in the formula below. Like 1st occurrence, 2nd occurrence, 3rd occurrence, etc.
Anyone have any suggestions here?
Thank you!
=IF($B$3="MTD",SUMPRODUCT((TMH!$A$3:$A$100=B7)*(MONTH(TMH!$B$1:$M$1)=MONTH($B$2))*(YEAR($B$2)=YEAR($B$2))*(TMH!$B$3:$M$100)))