Find dates with matching month and year on a row -> return value 2 columns to the right

Any help??

Originally Posted by :) Sixthsense :)

Not able to fix it and will be helpful if you attach a sample workbook.

Here it is:

Table.xlsx

Criteria: A1
Define name range A2:J2 - data


=IFERROR(INDEX(data,SMALL(IF(ISNUMBER(data),IF(YEAR(data)=YEAR($A$1),IF(MONTH(data)=MONTH($A$1),TRANSPOSE(ROW(INDIRECT("1:"&COLUMNS(data))))+2))),COLUMNS($A:A))),"")

Ctrl+Shift+Enter, not just enter
Copy across

Originally Posted by Teethless mama

Criteria: A1
Define name range A2:J2 - data


=IFERROR(INDEX(data,SMALL(IF(ISNUMBER(data),IF(YEAR(data)=YEAR($A$1),IF(MONTH(data)=MONTH($A$1),TRANSPOSE(ROW(INDIRECT("1:"&COLUMNS(data))))+2))),COLUMNS($A:A))),"")

Ctrl+Shift+Enter, not just enter
Copy across

I tried this and I only got 100 €. The formula should return 300 €, as there are two dates with same month and year (December 2012) between B2:J2.

Originally Posted by :) Sixthsense :)

In A2 cell

Formula:

=SUMPRODUCT(--(TEXT(B2:J2,"MMYYYY")=TEXT(A1,"MMYYYY")),D2:L2)

600 € with this formula, too..

Hi Cumbo

Not sure if i am on the right wave length. This will return the Euro value 2 cells to the right of the date in row 2 which corresponds to the date in A1, if no match is found then "No Match".

Formula:

=IFERROR(OFFSET($A$2,0,MATCH($A$1,$B$2:$J$2,0)+2,1,1),"No Match")

When a date with same year and month is found, it would return the value two columns right.

What does ''return the value two column to the right" mean? Do you want to write a formula in one cell and get the desired value in some other cells if the conditions are satisfied? It is not possible to write a formula in one cell and get the value in another cell.
If you require the output in the fashion which i had mentioned then the situation can either be handled using VBA or please change your data in the form of a table.

Please let me know if my understanding is wrong.

Originally Posted by Vinoth Kanna

What does ''return the value two column to the right" mean? Do you want to write a formula in one cell and get the desired value in some other cells if the conditions are satisfied? It is not possible to write a formula in one cell and get the value in another cell.
If you require the output in the fashion which i had mentioned then the situation can either be handled using VBA or please change your data in the form of a table.

Please let me know if my understanding is wrong.

The idea is simply to add all cells together, whose date is within the same month and year as the specified cell (in my table, cell A1). The date of each €-value cell is "two columns two the left of them". I.e. for the first date in column B, the corresponding € value is in column D. The second date is in column E and corresponding € value in column G etc.

If that is the case then the formula given by Sixthsense should work.

11307733.jpg

Hmmm... Doesn't work for me.

Originally Posted by Cumbo

Attachment 194734

Hmmm... Doesn't work for me.

Works now, had to replace "MMYYYY" with the Finnish version. Thanks a lot!


That was the good news and now for the other stuff:

the case I was trying to solve is more complex. For the sake of simplicity, I made another table which simplified the problem I had. I though I would get it working with the formula above, but turns out I was wrong.

In my actual table:
#1 there are more columns, from where the values must be picked to the other table
#2 the name in column B (sheet "Case") must correspond to that in column A, sheet "€"
#3 the value in column C (sheet "Case") must be "x"


What I am trying to accomplish:

Add all values from columns "Type 1 €", "Type 2 €" and "Type 3 €" (sheet "Case") on sheet "€". As mentioned above, the values added must also fulfill #2 and #3.

Originally Posted by :) Sixthsense :)

In B2 cell – Non Array Formula

This formula is worked for me

Formula:

=SUMPRODUCT(--(Case!$C$2:$C$23="x")*--(Case!$B$2:$B$23=$A2)*--(TEXT(Case!$F$2:$BC$23,"MMYYYY")=TEXT(B$1,"MMYYYY")),Case!$J$2:$BG$23)+SUMPRODUCT(--(Case!$C$2:$C$23="x")*--(Case!$B$2:$B$23=$A2)*--(TEXT(Case!$F$2:$BC$23,"MMYYYY")=TEXT(B$1,"MMYYYY")),Case!$K$2:$BH$23)+SUMPRODUCT(--(Case!$C$2:$C$23="x")*--(Case!$B$2:$B$23=$A2)*--(TEXT(Case!$F$2:$BC$23,"MMYYYY")=TEXT(B$1,"MMYYYY")),Case!$L$2:$BI$23)

The below one is for you in B2 cell

Formula:

=SUMPRODUCT(--(Case!$C$2:$C$23="x")*--(Case!$B$2:$B$23=$A2)*--(TEXT(Case!$F$2:$BC$23," KKVVVV")=TEXT(B$1,"KKVVVV")),Case!$J$2:$BG$23)+SUMPRODUCT(--(Case!$C$2:$C$23="x")*--(Case!$B$2:$B$23=$A2)*--(TEXT(Case!$F$2:$BC$23,"KKVVVV")=TEXT(B$1,"KKVVVV")),Case!$K$2:$BH$23)+SUMPRODUCT(--(Case!$C$2:$C$23="x")*--(Case!$B$2:$B$23=$A2)*--(TEXT(Case!$F$2:$BC$23,"KKVVVV")=TEXT(B$1,"KKVVVV")),Case!$L$2:$BI$23)

Drag it down and right.

Wow, it worked! Just had to remove one extra space inside the quote marks in the first sumproduct formula. Other than than, worked perfect. Thanks a lot!

=SUMPRODUCT(--(Case!$C$2:$C$23="x")*--(Case!$B$2:$B$23=$A2)*--(TEXT(Case!$F$2:$BC$23,"KKVVVV")=TEXT(B$1,"KKVVVV")),Case!$J$2:$BG$23)+SUMPRODUCT(--(Case!$C$2:$C$23="x")*--(Case!$B$2:$B$23=$A2)*--(TEXT(Case!$F$2:$BC$23,"KKVVVV")=TEXT(B$1,"KKVVVV")),Case!$K$2:$BH$23)+SUMPRODUCT(--(Case!$C$2:$C$23="x")*--(Case!$B$2:$B$23=$A2)*--(TEXT(Case!$F$2:$BC$23,"KKVVVV")=TEXT(B$1,"KKVVVV")),Case!$L$2:$BI$23)