CODEWITHSUNDEEP
excelindexingmatchsumifs
James0419
James0419

Reputation: 123

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

Upvotes: 1

Views: 18987

Answers (4)

David Zemens
David Zemens

Reputation: 53623

Put this formula in Cell D2 and drag down:

=IF(NOT(ISERROR(MATCH(A2,$E$2:$E$20,FALSE))),B2,"")

In row 22, change the month names to be the full name, OR in column C, change the month names to abbreviated 3-letter names, this is so we can make use of the SumIf function.

Then, in Cell B23, put this formula and drag it across:

=SUMIF($C$2:$C$16,B22,$D$2:$D$16)

(I put the formula in row 24 to check against the data you already had in Row 23)

Screenshot of SumIf function results

Upvotes: 1

barry houdini
barry houdini

Reputation: 46331

Shouldn't be much harder without a helper column. Just make sure you have full month name in row 22 to match column C data then use this formula in B23 copied across

=SUMPRODUCT(SUMIFS($B2:$B16,$C2:$C16,B22,$A2:$A16,$E2:$E20))

Upvotes: 0

jrad
jrad

Reputation: 3190

Both answers posted thus far are just fine, but just as an alternative, here's what I would do. Put this formula in cell D2 and drag it down:

=IF(NOT(ISNA(VLOOKUP(A2, $E$2:$E$20, 1, FALSE))), B2, "")

Then if you make a column containing the names of the months, say from G2 to G7 you have February, March, ..., July, you can put this formula into H2 and drag it down:

=SUMIFS($D$2:$D$16, $C$2:$C$16, G2)

That should get what you want.

Upvotes: 1

Simon1979
Simon1979

Reputation: 2108

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

Upvotes: 1

Related Questions