Summing data based on 2 criteria being met

Hi,

I'm going round and round in circles trying to get a formula to work.  I thought I needed a SUMIFS statement, but despite many attempts, I'm still getting a #VALUE return.  Here's what I'm trying to use:

=SUMIFS(AB:AB,Q:Q,"priority",B:B,"RCC East")

 

and what I want this to do is as follows:

I want to add up the figures in Column AB, if Column Q contains the text "priority" AND column B contains the text "RCC East".  If there are no instances where both of these are true, a "0" return would be useful but not essential.

 

Can anyone spot what I'm doing wrong?

Thanks!

Comments

Sumif

Rebrovsky | | Permalink

Where are you putting the sumif formula? i would guess you are putting it in the same column as the column you are summing (ab). This is the equivelant of a circular reference and returns #Value.

 

You need to specify the rows that you want to sum. So for example if you want to put the fromula and result in AB100 and sum all the rowsa above it starting at row 1, your formula should look like this

=SUMIFS(AB1:AB99,Q1:Q99,"priority",B1:B99,"RCC East")

 

This should work

 

 

 

RichardWhight's picture

SUMIFS

RichardWhight | | Permalink

Have you tried the formula as:

=SUMIFS(AB:AB,Q:Q,"=priority",B:B,"=RCC East")

 

 

shurst's picture

Should work

shurst | | Permalink

I think the formula should work as entered - is the formula shown in your post copied and pasted from Excel, or typed in again? Did you select the columns or type the references in?

RichardWhight's picture

SUMIFS

RichardWhight | | Permalink

Given that Reborvsky is correct then maybe also:

 

Revised Answer:

=SUMIFS(AB1:AB99,Q1:Q99,"=priority",B1:B99,"=RCC East")

 

Why? Because that's criteria. Suppose you wanted to negate this then you would write

=SUMIFS(AB1:AB99,Q1:Q99,"<>priority",B1:B99,"<>RCC East")

shurst's picture

Comparison operators

shurst | | Permalink

True if you are using a comparison operator such as <,> or <> but shouldn't be necessary for equals in my experience.

shurst's picture

Error in column AB

shurst | | Permalink

Given that the formula is correct, is there any chance that one of the cells being summed in column AB itself contains a #VALUE error?

RichardWhight's picture

Error

RichardWhight | | Permalink

I think that if any of the text in the column is actually non-text data then this will happen. Make sure that you format the whole column as text and that things that look like dates and numbers are of text type.

patvanaalst's picture

Many Thanks

patvanaalst | | Permalink

I never knew about this function, it looks like a viable alternative to conditional sums (which are very memory hungry!).  I'll bear this one in mind in future!

 

Cheers

Pat

shurst's picture

SUMIFS and COUNTIFS

shurst | | Permalink

SUMIFS() and COUNTIFS() were introduced in Excel 2007 - a bit more information and a glaring grammatical error here: http://www.accountingweb.co.uk/item/169813

jndavs's picture

A slightly less elegant solution

jndavs | | Permalink

A slightly less elegant solution would be to add an extra column with

=AND(Qnn,="priority",Bnn,="RCC East")

and use a normal =Sumif() adding up the TRUE values on this column.

Using this method you can have upto 256 conditions in the AND statement or replace AND for any other logical test.

 

bro0010's picture

Error

bro0010 | | Permalink

RichardWhight wrote:

I think that if any of the text in the column is actually non-text data then this will happen. Make sure that you format the whole column as text and that things that look like dates and numbers are of text type.

I came across a spreadsheet that had its user tearing their hair out with a similar issue.  Turns out the user had hit the spacebar and enter instead of pressing delete to remove a value.  We found it by highlighting the data area and executing Edit | Goto | Special | Constants | Text.  A real lifesaver!

 

Ian

www.onionrs.co.uk

Add comment
Log in or register to post comments
Group: ExcelZone
A gathering place for the Excel community to explore new ideas and techniques and a forum to debate product features and best practices.