# SUMIFS to match array of Criteria

1. ## SUMIFS to match array of Criteria

I am trying to use SUMIFS to return the sum of a revenue column if the adjacent column contains one of two possible values.

So let's say column A contains 5 lines of revenue figures.

Column B contains the status of a sales order that that revenue applies to. The order status can be either: Processing, Complete, Cancelled or Closed.

 REVENUE STATUS 22.30 Complete 19.99 Closed 100 Complete 56.50 Complete 39.25 Cancelled

How do I use the SUMIFS formula to get the total of revenue for the two orders that equal 'Cancelled' and 'Closed', so in this case, I want to see the figure 59.24.

I tried embedding an 'OR' statement, but that didn't seem to work.

Note that I'm using the SUMIFS formula because more criteria need to be added later, such as a date range.

2. ## Re: SUMIFS to match any of Criteria, not all.

Try this...

Formula:
`Please Login or Register  to view this content.`

3. ## Re: SUMIFS to match any of Criteria, not all.

That's the one. Thank you.

4. ## Re: SUMIFS to match any of Criteria, not all.

Oh wait, sorry. that doesn't work. If I do that, I only get the sum of the first entry in the array. So if I do {"Closed","Cancelled"} I get the sum of "Closed" revenue but not "Cancelled".

5. ## Re: SUMIFS to match array of Criteria

Somehow its true.. I thonk.. You are using only SumIF section.. not the outer SUM..

Try to use the above with Ctrl + Shift + Enter.. but i hope its not required..

still try one more approach.

Formula:
`Please Login or Register  to view this content.`

6. ## Re: SUMIFS to match array of Criteria

Oh, I get it now. You were correct, I was trying to use the SUMIF outside of the SUM formula. Your first solution was correct; apologies for the confusion.

7. ## Re: SUMIFS to match array of Criteria

Thanks for the feedback & Rep..

There are currently 1 users browsing this thread. (0 members and 1 guests)