SUMIFS formula returns #Value!

[AirborneBob]

I am trying to us the SUMIFS the formula to sum the values in one column based on the values in two other columns.


I am using the wizard and everytime I complete the formula, I get the #Value! as a result.


In checking on #Value! it states that there is a disparity between data types in the formula. However, I have checked, double checked and triple checked my data types and they are all General.


I have used this formula many times and I have never had this problem.


What am I doing wrong?

 

[Hui]

Bob

The General Format you are refering to relates to the Display Format, not the actual data format.


Check that your numbers are all nyumbers and not values by multiplying them in place by 1

Copy /Paste Special Multiply Values


Also there was abug in an early 2007 release, where if you had any blank rows in the ranges for the Sumifs, you would get that error, So put some values in them and then delete them seamed to fix that, give it a try anyway.


If the above doesn't help, Can you post the actual formula you are using and some data from the 2 or more ranges?

 

[AirborneBob]

I found out what I was doing wrong:


This is the formula that I was using:


=SUMIFS(E3:E12,A3:D12,A63,F3:H12,A64)


The problem was I am using merged cells and when I was usign the wizard to help with the formula, I was dragging down the column and grabbing all of the cells and that was screwing up my calculations.


I put in the following formula just using the first cell in the merged cell group and that solived my problems.


Thanks to Hui for replying and trying to solve my problem. I have read many of your posts and have applied some of your wisdom to my own Excel Spreadsheets.


=SUMIFS(E3:E12,A3:A12,A63,F3:F12,A64)