Regarding =SUMIF and prioritising data

[kenchany]

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?

Many thanks in advance for your help!
-K

 

[Mackers]

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.

 

[Zakkaroo]

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.

 

[kenchany]

Thank you for your answers!
It's nice to know such a supportive forum exists!

-K