Search code examples
excelindexingmatchsumifs

Sum only matched values from two columns

enter image description here

Hello Everyone,

Does anyone know how to sum the Hours from column B (Hours) if column A numbers matched with column E and sum by months.

Example, take February data as an example, since only 3000 & 4000 existed in both column A & column E, that's why I need to sum the hours (20+10=30) from column B by it's month below.

I also attached the example excel sheet below. https://www.dropbox.com/s/0s51g1i8g6s6e2d/Test.xls?m

Thanks in advance. :-)

Solution

  • You could get complicated with customer functions but if having an extra column (potentially hidden) doesn't matter then this would work:

    In cell D2 enter:

    =iferror(vlookup($A2,$E$2:$E$20,1,FALSE),"x")

    Drag the formula down.

    Under each month you can then put:

    =sumifs($B$2:$B$16,$C$2:$C$16,B$22,$D$2:$D$16,"<>x")

    Drag the formula across

    This assumes you can change the month format in either column C or row 22 to be the same, ie. Jan, Feb, Mar or January, February, March etc.

    Written on the fly and not tested so excuse any minor errors...