=SumIf() not calculating correctly

Question

So I've trawled the internet as best as I can but I have been unable to find a solution to my problem.

I have a table of Headcounts and SumIf formulas in order to calculate the total availability for a project. The SumIf formula works for the first 5 results, but gives me a erroneous result on the last.

Example Below:

Type	Project 1	Project 2	Project 3	Project 4	Project 5	Project 6	Total
IP	3000	11000	1400000	250000	30000	45000	1739000
Sec	1500	0	0	0	0	380000	381500

Each project has its own sheet with a variety of data on it, one column being the "Type" (Column J) and another being "Availability" (Column L). (That matches up with the information on this table)

So the SumIf I am using is "=SUMIF('Project 6'!$J:$J, "Sec",'Project 6'!$L$L)"

Which gives me 380000. However, when I go onto the sheet, select the column J, and filter for "Sec", 4 results show, only adding up to 1500, way off the 380000 its somehow counting. Yet Project 1 is calculating correctly, where the only difference in the formula is the sheet its relating to...

I'm convinced I'm doing something stupid, but I honestly cannot see what it is!

Thanks for your help!

Answer 1

Assuming you have copy+pasted your actual formula here (from your .xlsx file), the : between the $L$L is missing.

Try replacing  $L$L with $L:$L  in your formula. 

Your formula should read   "=SUMIF('Project 6'!$J:$J, "Sec",'Project 6'!$L:$L)"

Answer 2

Hi,

=SUMIF('Project 6'!$J:$J, "Sec",'Project 6'!$L:$L)

Answer 3

Ahh I feel so stupid, something as small as that! Thanks guys!