# 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!

### Sumif

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

### SUMIFS

Have you tried the formula as:

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

### Should work

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?

### SUMIFS

Given that Reborvsky is correct then maybe also:

=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")

### Comparison operators

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

### Error in column AB

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?

### Error

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.

### Many Thanks

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

### SUMIFS and COUNTIFS

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