Summing data based on 2 criteria being met

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

Have you tried the formula as:

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

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?

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

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

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?

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 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() were introduced in Excel 2007 - a bit more information and a glaring grammatical error here: http://www.accountingweb.co.uk/item/169813

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.