Summing functions not working

[NS4Excel]

Summing Data.xlsx
I have attached a file that has me totally stumped. Please look at the two summary sheets. You will see on the "Summary" tab there's a formula in cell J7. It is calculating properly. That same exact formula is in cell J8 and I 0 as the result. There is absolutely no difference between the formulas. You can then see on the "Summary2" tab that cells J5-J13 also contain that exact same formula. You'll see that sometimes I get the right result and other times I get a 0. Both sheets also at times give me #VALUE! as a result. I'm confused how the EXACT same formula can give different results cell location of where the formula is placed.

Then another strange occurence:
On the "Summary" sheet in cells J17-23 is a different sum formula. This one is only giving me the sum of one column even though I've asked it to sum 6 columns. Can anyone tell me why my Excel is not cooperating and what I can do to fix it?

Thanks so much.

[Crooza]

Becasue it's reading the array as True and Falses - this is what it looks like if you expand it out.

=IF({TRUE;FALSE;TRUE;FALSE;TRUE;FALSE},SUM(Details!$L$7:$Q$12),0)

So you get True = the answer is returned, then false, 0 is rteturned then after 3 iterations of this it runs out of true/false options and returns an error

[Debraj Roy]

However you have not mentioned.. what will be your expected result..

can you please reupload.. with expected result.. (manualy )

[NS4Excel]

Thanks for your response. I assume the response is to the formula =IF(Details!$K$7:$K$12="Cap",SUM(Details!$L$7:$Q$12),0). Do you know why the regular SUMIF formula is not working?

[Crooza]

Thanks for your response. I assume the response is to the formula =IF(Details!$K$7:$K$12="Cap",SUM(Details!$L$7:$Q$12),0). YES Do you know why the regular SUMIF formula is not working? I assume because your sum range has multiple columns One way to fix that might be to sum across all the columns first then put this final column in your sumif formula (as the third range)

See above for my best guess

[NS4Excel]

Debra: The expected result is $74. What I want it to do is sum the data in L7 to Q12 if it finds "Cap" in cells K7 to K12. If I can get this simple formula to work I can then expand my range. Thanks.

[Crooza]

OK this will work as an array formula (Cntl + Shift + Enter) - Squiggly brackets - BUT and it's a big BUT you can't have blanks in your cells, you need to fill them with zeros

=SUMPRODUCT(MMULT(L7:Q12,TRANSPOSE(COLUMN(L7:Q12)^0)),IF(K7:K12="Cap",1,0))

I removed sheet references too just to make it easier