Sumifs problem

**kim296** · 04-01-2013, 05:17 PM

I am using Sumifs to total up a different table into one report. I have three criteria to match up and the formula works fine on all of my columns, except 1.

The table that I am having trouble has a formulated total (Quantity*price). I'm trying to sumif the total price, but it errors out.
I know that the formula works because I can pull the each amount, but it errors everytime I try to pull the total price column.

Does anyone have suggestions on making this work properly.

Thanks

**Richard Buttrey** · 04-01-2013, 05:36 PM

Hi,

We need to see the workbook. Please upload and clearly show which cells you are referring to and what results you expect.

**Harribone** · 04-01-2013, 05:39 PM

Can you explain in detail please. What is the formula as you have typed it for example?

It should be in the format:

=SUMIFS(sumrange,range1,criteria1,2,2,3,3 etc)

sumrange is the range you are totalling (eg. A1:A5) range 1 is the first range you check (eg B1:B5) and criteria1 is what range1 needs to be (eg ">"&5), then you can repeat the other range/criterias in the same format.

**kim296** · 04-01-2013, 07:12 PM

=SUMIFS('PROPERTY EXPENSE'!$CG$11:$CG$2510,'PROPERTY EXPENSE'!$K$11:$R$2510,">="&'CASH FLOW ANALYSIS'!$G$5,'PROPERTY EXPENSE'!$K$11:$R$2510,"<="&'CASH FLOW ANALYSIS'!$G$6,'PROPERTY EXPENSE'!$C$11:$J$2510,"="&$B13)

The >= and <= are date criteria and the = is the property id number.

This formula works for my other pages (Payments & taxes). The above formula works with CG being the (each price), but if I try to change my formula to reflect the total price (CH) v/s the each price (CG)- it errors out. My total price is "=quantity * each price".

I can make this formula work everywhere except my total column. The only thing that I can figure is because there is a formula in that column. What I'm trying to do is make a general totals page for rental property (tables: payments recv'd, mortgage- tax- ins and property expense).

I hope this makes more sense and someone will be able to help. The file is extremely large, so I'm not uploading it. Any help appreciated.

**Richard Buttrey** · 04-02-2013, 05:10 AM

Originally Posted by kim296

The file is extremely large, so I'm not uploading it. Any help appreciated.

Why can't you remove all but a representative sample of the rows? It's much easier to track down your error if we can see the problem in context. We obviously can't tell from your formula what values exist in the various ranges and need to see them.

**daddylonglegs** · 04-02-2013, 05:51 AM

Originally Posted by kim296

=SUMIFS('PROPERTY EXPENSE'!$CG$11:$CG$2510,'PROPERTY EXPENSE'!$K$11:$R$2510,">="&'CASH FLOW ANALYSIS'!$G$5,'PROPERTY EXPENSE'!$K$11:$R$2510,"<="&'CASH FLOW ANALYSIS'!$G$6,'PROPERTY EXPENSE'!$C$11:$J$2510,"="&$B13)

This formula will always return #VALUE! error whatever the data. SUMIFS requires all ranges within the formula to be the same size and shape (see Excel help for SUMIFS). In the quoted formula the range to sum (the first one) is a single column but the other ranges are all 8 columns wide. either all ranges should be a single column or all ranges should be 8 columns, given your description I'm not sure which of those applies.

If you really have a situation where you want to sum a single column based on criteria in 8 columns then you can't use SUMIFS - a version with SUMPRODUCT may work but you need to describe how you expect an 8 to 1 relationship to work, e.g. do you sum once only if all 8 columns meet the criteria, or sum for each column that meets the criteria....or sum once if any of the columns meet it?