# Sumproduct meeting two Criteria

1. ## Sumproduct meeting two Criteria

Hi all,

I'm working on a spreadsheet at work and I'm trying to sum only the values in a column that meet criteria in the column next to it. For example, in the attached spreadsheet, I would like to have a totals line at the bottom of the spreadsheet for all three Facilities and the total next to them. I know I could do a pivot table but I know this approach would be more aesthetic and easier for my supervisor.

Thanks for the help!

2. ## Re: Sumproduct meeting two Criteria

If you already have the unique locations....

 A B 26 Facility 1 \$ 63,196.00 27 Facility 2 \$ 41,320.00 28 Facility 3 \$ 16,707.00

B26=SUMIF(\$A\$2:\$A\$22,A26,B2:\$B\$22)
copied down

If you dont have unique locations, use this...
A26=IFERROR(INDEX(\$A\$2:\$A\$22,MATCH(0,INDEX(COUNTIF(\$A\$25:A25,\$A\$2:\$A\$22),0,0),0)),"")
copied down

3. ## Re: Sumproduct meeting two Criteria

Wow, that worked like a charm. Thanks so much!

4. ## Re: Sumproduct meeting two Criteria

Sorry, there waqs an error in that 1st formula (left out a \$). If should have been...
=SUMIF(\$A\$2:\$A\$22,A26,\$B\$2:\$B\$22)

IF you want the sumproduct equivalent....
=SUMPRODUCT((\$A\$2:\$A\$22=A26)*(\$B\$2:\$B\$22))

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