# Using countif in a complex sumif function

#### i_excel

##### Board Regular
Hi

I seem to be tied up on problems of a similar nature. In this one, I am interested in working out whether or not countif can be used as a condition in a complex sumif function.

Here is an example to clarify my question:

A B C
1 C 4
2 I 5
3 C 6

I am interested in a formula that

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

THAT WHAT?! lol

#### i_excel

##### Board Regular
Here is the question in full:

Hi

I seem to be tied up on problems of a similar nature. In this one, I am interested in working out whether or not countif can be used as a condition in a complex sumif function.

I am interested in the equivalent of a sumifs function in which the sum range is instead the result of a calculation involving two cells of data, and one of the conditions is a countif function of another array of data.

Here is an example to clarify my question:

A B C D
1 C 4 ?
2 I 5 ?
3 C 6 ?

The desired formula would be a functioning version of:
sumifs( (a1+c1)*c1, countif(\$b\$1:\$b\$3,b1),>1). It would give the following values in column D: 20; 0; 54.

The desired formula would also allow modification of the calculation (a1+c1)*c1, so that different calculations could be performed (such as with more than two columns of data) with the same countif type conditionality.

Let me know if I have been unclear and I will try to rephrase my difficulty.

On a more general note, I would appreciate any interpretation of suitable formulas since I come across problems of a similar nature often but don't believe I understand how they work and can be adapted.

Kind regards

i_excel

#### svendiamond

##### Well-known Member
The SUMIFS function is formatted as:

=SUMIFS(sum_range,criteria_range1,criteria1,etc...)

So your first part needs to be a range. What range do you want to SUM? "(A1+C1)*C1" will return a number, not a range of cells...

#### i_excel

##### Board Regular
Good point, apologies - I am obviously failing to grasp the problem I am interested in solving. I should make clear that I realise that the sumif function cannot be used in the way I hope - I am simply using it to communicate the kind of function I wish to formulate.

The formula should be some *working equivalent of
sumifs( (a1:a3+c1:c3)*c1:c3, countif(\$b\$1:\$b\$3,b1),>1)
And so for the above example, the results in column D should be 74; 0 ; 74.

To clarify it: The calculation performed in D1 would be: ((1+4)*4) + ((3+6)*6) = 74, since the value in b1 (C) appears in both b1 and b3.

Let me know if that is still nonsense.

#### svendiamond

##### Well-known Member
Let me try to understand... are you saying that for every instance that the value in column B occurs within column B, you want to add column A to column C then multiply that by column C?

((A1+C1)*C1)+((A3+C3)*C3)) for the first example? Is this correct?

#### i_excel

##### Board Regular
Hi, yes that is correct - although my interpretation of it differs a little: calculate the sum of the calculation (a#+c#)*c# for each observation whose value in column b is equal to b#.

I don't know if that helps at all..

i_excel

#### svendiamond

##### Well-known Member
Can you post an example along with a column (D) titled "expected results" and enter in the expected results for each row?

#### i_excel

##### Board Regular
Sure:

 a b c expected results 1 C 4 74 2 I 5 0 3 C 6 74

<tbody>
</tbody>

Apologies for the poor formatting, I am a novice.

#### pgc01

##### MrExcel MVP
...
The desired formula would also allow modification of the calculation (a1+c1)*c1, so that different calculations could be performed (such as with more than two columns of data) with the same countif type conditionality.

Let me know if I have been unclear and I will try to rephrase my difficulty.
...

Hi

Not clear what you need in case of more than 2 columns of data.
For ex., would you have something like this? In this case what would be the logic of the formulas and the expected results?

Replies
2
Views
928
Replies
3
Views
296
Replies
9
Views
403
Replies
3
Views
247
Replies
13
Views
635

1,195,663
Messages
6,011,013
Members
441,579
Latest member
satishrazdhan

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