Value error using sumifs/sumproduct

[drluke]

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

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]1000[/TD]
[TD]2000[/TD]
[TD]3000[/TD]
[TD]Total[/TD]
[TD]1000[/TD]
[TD]#[/TD]
[/TR]
[TR]
[TD]Emp#[/TD]
[TD]Salaries[/TD]
[TD]Bonuses[/TD]
[TD]Pension[/TD]
[TD]Total[/TD]
[TD]Salaries[/TD]
[TD]Ben A[/TD]
[/TR]
[TR]
[TD]121[/TD]
[TD]1568.98[/TD]
[TD]500.00[/TD]
[TD](29.65)[/TD]
[TD]2039.33[/TD]
[TD]150.00[/TD]
[TD]0.00[/TD]
[/TR]
</tbody>[/TABLE]

My summary sheet is set up:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]1000[/TD]
[TD]2000[/TD]
[TD]3000[/TD]
[/TR]
[TR]
[TD]Emp#[/TD]
[TD]Salaries[/TD]
[TD]Bonuses[/TD]
[TD]Pension[/TD]
[/TR]
[TR]
[TD]121[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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

 

[RoryA]

What formula are you using?

 

[drluke]

=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]

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]

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

 

[RoryA]

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

 

[drluke]

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

 

[RoryA]

On both sets of criteria?