# Sum with more than one column criteria

1. ## Sum with more than one column criteria

I have some data that looks like this:

 A B C Project No. Root Project Compensation 000016.000 000016 80705 000016.001 000016 8000 000016.002 000016 2295 000016.003 000016 16440 000017.000 000017 35000 000017.001 000017 1500

I want to sum the Compensation for all the sub-numbers in the Project No. column of the Root Project number and have them display on the same row as the .000 number. In other words, I want to sum the Compensation for Project Nos 000016.000 + 000016.001 + 000016.002 + 000016.003 and have it show up on the same row (unshown column D) as the .000 Project No. in column A, otherwise leave the field blank. This would result in a total of 107440 (sum of 000016.000 thru 000016.003) in the first row with all the other Project No. 000016 rows being blank. So basically, if Root Project = LEFT(Number column) AND Project No. has .000 at the end of it show me the Compensation total of all Project Nos that start with 000016 on the 000016.000 row in column D. Hope that makes sense.

I have tried different variations of SUMIFS, SUMPRODUCT, and nested LEFT statements in SUMIFS with no luck.

2. ## Re: Sum with more than one column criteria

Try:
=IF(RIGHT(A2,4)=".000",SUMPRODUCT(--(LEFT(\$A\$2:\$A\$7,6)=B2)*\$C\$2:\$C\$7),"")

3. ## Re: Sum with more than one column criteria

Lovely! Thanks so much!

4. ## Re: Sum with more than one column criteria

You're welcome.

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

5. ## Re: Sum with more than one column criteria

maybe
with Power Query
 List Root Project Compensation 000016.000, 000016.001, 000016.002, 000016.003 16 107440 000017.000, 000017.001 17 36500

There are currently 1 users browsing this thread. (0 members and 1 guests)