# Regarding =SUMIF and prioritising data

#### kenchany

##### New Member
Hello,

I apologize if this has been answered elsewhere, as I'm not sure how to phrase the problem, let alone search the forums for pertinent answers.

I have a column of revenue sorted according to category. The months in which the revenue amounts will be paid are listed in another column.

 A B C 1 Budgeted Settlement Month paid In 2 10,000 0 July 3 30,000 0 August 4 30,000 0 September 5 50,000 0 August 6 10,000 0 September 7 130,000 8 9 July August September 10 10,000 80,000 40,000

<tbody>
</tbody>

So far, all that needed to be done was to add up the monthly totals using SUMIF with the months as the criteria. I'd get something like =SUMIF(C2:C6,"July",A2:A6) and that was fine.

Now, with the Settlement column (B) entering the picture, I get confused.
I would like to find the total for July with some figures entered in Budgeted (A) and others in Settlement (B). If column B has figures entered, I'd like to prioritise those figures instead of the Budgeted (A) figures.

 A B C 1 Budgeted Settlement Month paid In 2 10,000 0 July 3 30,000 25,000 August 4 30,000 0 September 5 50,000 0 August 6 10,000 12,000 September 7 130,000 8 9 July August September 10 ? ? ?

<tbody>
</tbody>

So, instead of adding 30,000 in A3, I'd like to add 25,000 from B3 instead, as we know that figure to be the actual amount. Would I still use the SUMIF function, or an altogether different one?

-K

### Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Hi K

Lots of different ways to do this one. A simple way would be to add a column in D2 that says =IF(B2<>0, B2, A2) and then change your SUMIFS to reference column D rather than A or B.

Alternatively, as an array function (to execute the function press control+shift+enter):

=SUM(IF(C\$2:C\$10="July",IF(B\$2:B\$10<>0,B\$2:B\$10,A\$2:A\$10)))

The way this works is you are creating an array that returns FALSE when July isn't in column C (first part), the value in B if B is not equal to zero (second part), and the value in A otherwise (third part). It then performs SUM() over this array.

Last edited:
If it's possible to add a column in, I would just do that.

Let's imagine the new column is A and your current A & B have been shifted right into B&C

A2 =if(C2=0,B2,C2)

Fill down

Now Column A always contains the values you want to Sum and you can use your original formula.

It's nice to know such a supportive forum exists!

-K

Replies
6
Views
262
Replies
4
Views
498
Replies
2
Views
324
Replies
11
Views
760
Replies
3
Views
350

1,196,481
Messages
6,015,451
Members
441,896
Latest member
clomah

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back