CODEWITHSUNDEEP
excelexcel-formula
reddolo89
reddolo89

Reputation: 13

Excel - How to sum rows in table A if they meet a condition in table B

Goal: Calculate how much money I spent per TYPE of expense (food, vacation, house, etc.) taking the values from a table in which expenses are filled out.

I have 2 tables: Table A for the total amount spent per TYPE of expense Table B with all expenses.

enter image description here

What Formula can I use to have in the cells C4, C5, C6, C7, C8 (blue arrow in the pic) the sum of each row that match the Column O "TYPE"?

I have strong difficulties to describe what I'm asking, but I can use an example:
in cell C4 (food), should appear the sum of "Things 3" and "Things 4" (which is: 47,56 + 10,00 + 60,00) because on Column O (TYPE) there is an F

I searched how to upload a file to make it easier to edit it, but I couldn't find the function.

I googled for hours how to use SUMIFS, INDEX, MATCH, but I am not even sure that it is right. Mostly I find solutions if table A and table B have the same size, but in my file they aren't the same size.

The only solution that I could find was to use =+IF(COUNTIF(I5:O5;"F")>=1;SUM(I5:N5);0) in cell C4

This would work if there were really few rows like in the picture. However the original file has over 40 rows and this makes it ridiculously long.

Thank you so much for your help!!

Upvotes: 1

Views: 810

Answers (3)

bosco_yip
bosco_yip

Reputation: 3802

In C5 formula copied down :

=SUMPRODUCT(ISNUMBER(SEARCH(LEFT(B5),O$5:O$9))*I$5:N$9)

Upvotes: 0

Puno
Puno

Reputation: 61

I think the issue may be the structure of your Table, you can try unpivoting the table so that each amount is a line item instead of a column. Or try using a pivot table.

Upvotes: 0

JvdV
JvdV

Reputation: 75850

In C5, use:

=SUMPRODUCT((ISNUMBER(FIND(UPPER("("&O$5:O$9&")"),B5)))*I$5:N$9)

Drag down.

Upvotes: 1

Related Questions