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:

	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?

 

[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?