SUMPRODUCT to find nth value

[Pineapple_Crazy]

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)))

 

[James006]

Hello,

In order to have the flexibility of the nth instance, you will probably need an Array Formula with Large() or Small() function ... :wink:

 

[Eric W]

Maybe like this:

=INDEX(TMH!$B$3:$M$100,SMALL(IF(TMH!$A$3:$A$100=B7,ROW(TMH!$A$3:$A$100)-ROW(TMH!$A$3)+1),B8),MATCH(B2,TMH!$B$1:$M$1))
confirmed with Control+Shift+Enter.

where n is in B8. This also assumes that the dates in B1:M1 are the first of the month. If not, this will work:

=INDEX(TMH!$B$3:$M$100,SMALL(IF(TMH!$A$3:$A$100=B7,ROW(TMH!$A$3:$A$100)-ROW(TMH!$A$3)+1),B8),SMALL(IF(MONTH(TMH!$B$1:$M$1)=MONTH(B2),COLUMN(TMH!$B$1:$M$1)-COLUMN(TMH!$B$1)+1),1))
with CSE.

You may have some issues with the way the dates are set up, since this condition:

(YEAR($B$2)=YEAR($B$2))

from your original formula does nothing. How are the dates set up in B1:M1, and what is the date in B2?

 

[Pineapple_Crazy]

Hey Guys,

Thanks! Eric, you are correct about the year portion it should be like this (YEAR(TMH!$B$1:$M$1)=YEAR($B$2)). The problem is I have two drop down boxes. One to select MTD, QTD, and YTD and another to select a date like JAN-2018 (1/1/2018). The formula must feed off of these values selected to sum things up appropriately. The full formula is found below. It's just the value at B7 can be found multiple times in the list and want to be able to just get the first occurrence or fourth occurrence, at my choosing. I guess I'm just not sure how to incorporate the formulas you provided here to make that work. I wish I could upload the workbook, so that you could see what I mean.


=IF($B$3="MTD",SUMPRODUCT((TMH!$A$3:$A$100=B7)*(MONTH(TMH!$B$1:$M$1)=MONTH($B$2))*(YEAR(TMH!$B$1:$M$1)=YEAR($B$2))*(TMH!$B$3:$M$100))/$C$5,IF($B$3="YTD",SUMPRODUCT((TMH!$A$3:$A$100=B7)*(MONTH(TMH!$B$1:$M$1)<=MONTH($B$2))*(YEAR(TMH!$B$1:$M$1))*(TMH!$B$3:$M$100))/$C$5,IF($B$3="QTD",SUMPRODUCT((TMH!$A$3:$A$100=B7)*((ROUNDUP(MONTH(TMH!$B$1:$M$1)/3,0)=ROUNDUP(MONTH($B$2)/3,0))*(YEAR(TMH!$B$1:$M$1)=YEAR($B$2))*(TMH!$B$3:$M$100))/$C$5))))

 

[Eric W]

You can show a sample of your workbook by using one of the tools at the HTML Maker link in my signature.

In the meantime, you can try to replace your formula with:

=IF($B$3="MTD",SUMPRODUCT((TMH!$A$3:$A$100=B7)*(MONTH(TMH!$B$1:$M$1)=MONTH($B$2))*(YEAR(TMH!$B$1:$M$1)=YEAR($B$2))*(TMH!$B$3:$M$100))/$C$5,IF($B$3="YTD",SUMPRODUCT((TMH!$A$3:$A$100=B7)*(MONTH(TMH!$B$1:$M$1)<=MONTH($B$2))*(YEAR(TMH!$B$1:$M$1)<=YEAR(B2))*(TMH!$B$3:$M$100))/$C$5,IF($B$3="QTD",SUMPRODUCT((TMH!$A$3:$A$100=B7)*((ROUNDUP(MONTH(TMH!$B$1:$M$1)/3,0)=ROUNDUP(MONTH($B$2)/3,0))*(YEAR(TMH!$B$1:$M$1)=YEAR($B$2))*(TMH!$B$3:$M$100))/$C$5),INDEX(TMH!$B$3:$M$100,SMALL(IF(TMH!$A$3:$A$100=B7,ROW(TMH!$A$3:$A$100)-ROW(TMH!$A$3)+1),B3),MATCH(B2,TMH!$B$1:$M$1)))))

confirmed with Control+Shift+Enter. Then change your drop-down list in B3 to include numbers, like {"MTD","QTD","YTD",1,2,3,4,5}.

I'm not sure if that's exactly what you want, and I had some issues testing it, especially the QTD section. But it might be worth looking at.

 

[Pineapple_Crazy]

Hey Eric,

Thanks again! I think this is getting closer, but it seems to sum everything in the column that has a value associated with B7. Did it do this to you when you tried it?

Thanks!

 

[Eric W]

No, it returned a single value. I'm not sure how it can return a sum, since it's an INDEX formula, not SUMPRODUCT. I'm not sure where to go from here without seeing a sample of your data showing the incorrect values.

 

[Pineapple_Crazy]

Here's an example of some data below. I'm taking into account "MTD" and the date of Feb-18 (bold below) to sum values based on a text value ("Available Rooms" in this case). This would sum only for the month of February. Using the formula below I was hoping just to return 4,789 which is the second value of "Available Rooms" found for February. However, this adds up both values for February returning 8,037.

=IF($A$3="MTD",SUMPRODUCT(($A$6:$A$9=A13)*(MONTH($B$4:$M$4)=MONTH($A$2))*(YEAR($B$4:$M$4)=YEAR($A$2))*($B$6:$M$9)),INDEX($B$6:$M$9,SMALL(IF($A$6:$A$9=B13,ROW($A$6:$A$9)-ROW($A$6)+1),2),MATCH(A2,$B$4:$M$4)))

Feb-18
MTD
	JAN 2018	FEB 2018	MAR 2018	APR 2018	MAY 2018	JUN 2018	JUL 2018	AUG 2018	SEP 2018	OCT 2018	NOV 2018	DEC 2018
Available Rooms	3,596	3,248	3,596	3,480	3,596	3,480	3,596	3,596	3,480	3,596	3,480	3,596
Occupied Rooms	2,460	2,344	3,262	2,982	3,288	3,205	3,363	3,295	3,076	3,126	2,524	2,502
Occupancy	68.4%	70.2%	90.2%	89.2%	89.6%	93.1%	96.0%	94.8%	89.8%	87.8%	0.0%	0.0%
Available Rooms	2,555	4,789	2,222	1,111	3,125	3,480	3,596	3,596	3,480	3,596	3,480	3,596
Available Rooms	8,037	8,037

<tbody>
</tbody>

^---Formulas found here---^

 

[Eric W]

I'll be away for a few days. I'll check when I can to see if this has been resolved.

 

[Pineapple_Crazy]

I'll be away for a few days. I'll check when I can to see if this has been resolved.

I did find another solution for this by adding another column to get it to sum on two values. Would be curious to know if there was a way to make that formula work though without having to add in an additional column. Thanks bud!