# Value error using sumifs/sumproduct

#### drluke

##### Active Member
I'm trying to sum columns/rows with multiple criteria using sumifs and sumproduct, but get value errors. My data table looks like this:

 1000 2000 3000 Total 1000 # Emp# Salaries Bonuses Pension Total Salaries Ben A 121 1568.98 500.00 (29.65) 2039.33 150.00 0.00

<tbody>
</tbody>

My summary sheet is set up:

 1000 2000 3000 Emp# Salaries Bonuses Pension 121

<tbody>
</tbody>

I am adding totals for salaries, bonuses, pension etc per employee number but get value errors. How can I correct this?

### Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

#### RoryA

##### MrExcel MVP, Moderator
What formula are you using?

#### drluke

##### Active Member
=SUMPRODUCT((Sheet1!A3:A50=A3)*(Sheet1!B1:G1=B1),B3:G50) the result here = 0 instead of 1,718.98

=SUMIFS(Sheet1!B3:G50,A3:A50,A3,Sheet1!B1:G1=B1) = #VALUE !

#### RoryA

##### MrExcel MVP, Moderator
The first one should work as long as the final argument is Sheet1!B3:G50 rather than just B3:G50.

If it doesn't work, then you've got a mismatch in your criteria - for example, numbers stored as text on one sheet and not the other, or leading/trailing spaces in the ID number.

#### drluke

##### Active Member
Thank you. I'm at a loss - the format for all the row/column references on both sheets are General .....

#### RoryA

##### MrExcel MVP, Moderator
If you clear any manual alignment of the cells, are they both either left or right aligned?

#### drluke

##### Active Member
Both are left aligned. I did =ISTEXT on both and the result was TRUE for both

#### RoryA

##### MrExcel MVP, Moderator
On both sets of criteria?

1,095,926
Messages
5,447,336
Members
405,447
Latest member
WPY