Too few arguments

[generaleddie]

Hi,
I'm getting the error too few arguments for this function with the below formula, any tips on where I am going wrong? I have simplified my table below but, basically, have multiple trusts donating to different charities and want to know the distinct count of beneficiaries for each charity, named in the table as One, Two and Three. In table below, for example, the results would be: One - 4, Two - 2, Three - 1.

=SUMPRODUCT(('Data £'!F:F="Three")/(COUNTIFS('Data £'!C:C, 'Data £'!C:C, 'Data £'!F:F="Three")+('Data £'!F:F<>"Three")))

[TABLE="width: 50"]
<tbody>[TR]
[TD]C[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]Organisation[/TD]
[TD]Charities[/TD]
[/TR]
[TR]
[TD]Action Duchenne Limited[/TD]
[TD]One[/TD]
[/TR]
[TR]
[TD]Army Family Federation[/TD]
[TD]Three[/TD]
[/TR]
[TR]
[TD]Build Change [/TD]
[TD]One[/TD]
[/TR]
[TR]
[TD]Bursary Students[/TD]
[TD]Two[/TD]
[/TR]
[TR]
[TD]Bursary Students[/TD]
[TD]Two[/TD]
[/TR]
[TR]
[TD]Bursary Students[/TD]
[TD]Two[/TD]
[/TR]
[TR]
[TD]Bursary Students[/TD]
[TD]Two[/TD]
[/TR]
[TR]
[TD]Bursary Students[/TD]
[TD]Two[/TD]
[/TR]
[TR]
[TD]Bursary Students[/TD]
[TD]Two[/TD]
[/TR]
[TR]
[TD]Bursary Students[/TD]
[TD]Two[/TD]
[/TR]
[TR]
[TD]Bursary Students[/TD]
[TD]Two[/TD]
[/TR]
[TR]
[TD]Bursary Students[/TD]
[TD]Two[/TD]
[/TR]
[TR]
[TD]Bursary Students[/TD]
[TD]Two[/TD]
[/TR]
[TR]
[TD]Bursary Students[/TD]
[TD]Two[/TD]
[/TR]
[TR]
[TD]Bursary Students[/TD]
[TD]Two[/TD]
[/TR]
[TR]
[TD]Cambridge Heath Sixth Forms [/TD]
[TD]Two[/TD]
[/TR]
[TR]
[TD]Chailey Heritage Foundation[/TD]
[TD]One[/TD]
[/TR]
[TR]
[TD]Children with Cancer UK[/TD]
[TD]One[/TD]
[/TR]
</tbody>[/TABLE]

TIA!

 

[KennyGreens]

Hi, your COUNTIFS formula is off, you can't have only 3 arguments, you need to have either 2 or 4 (or any even number for that matter).

 

[Gerald Higgins]

Hi, welcome to the board.

I think part of the problem - maybe not the only problem - is with your COUNTIFS function.

This slight variation at least doesn't return the "too few arguments" message . . .

=SUMPRODUCT(('Data £'!F:F="Three")/(COUNTIFS('Data £'!C:C, 'Data £'!C:C, 'Data £'!F:F,"Three")+('Data £'!F:F<>"Three")))

but I really don't know if it's doing what you want.

 

[sandy666]

is that what you want?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Charities[/td][td=bgcolor:#DDEBF7]Distinct Count of Organisation[/td][/tr]

[tr=bgcolor:#FFFFFF][td]One[/td][td]

4​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Three[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Two[/td][td]

2​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Grand Total[/td][td=bgcolor:#DDEBF7]

7​

[/td][/tr]
[/table]

 

[generaleddie]

is that what you want?

[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #DDEBF7"]Charities[/TD]
[TD="bgcolor: #DDEBF7"]Distinct Count of Organisation[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]One[/TD]
[TD]

4​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]Three[/TD]
[TD]

1​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]Two[/TD]
[TD]

2​

[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #DDEBF7"]Grand Total[/TD]
[TD="bgcolor: #DDEBF7"]

7​

[/TD]
[/TR]
</tbody>[/TABLE]

Yes, that is the point I want to get to.
Why does the COUNTIFS formula have to have an even number of arguments?

 

[Joe4]

Why does the COUNTIFS formula have to have an even number of arguments?

Because that is how it works!

You have "sets" of conditions:
1. criteria range
2. criteria

So if you have 1 "set", that is 2 arguments. If you have 2 "sets", that is 4 arguments, etc.
You cannot have a criteria range without corresponding criteria, or criteria with its corresponding criteria range.
So it is impossible to have an odd number of arguments. If you try, you will get errors.

You can read up on COUNTIFS here: https://www.techonthenet.com/excel/formulas/countifs.php

 

[sunilbsrv2k]

Yes, that is the point I want to get to.
Why does the COUNTIFS formula have to have an even number of arguments?

I guess that's because, the syntax says Countifs(crieria1_Range1, criteria1....). Which means, for each range specified, there should be a criteria added.

 

[Gerald Higgins]

You can also get help on any Excel function by starting to type in the function name (e.g. =COUNTIFS( ) and then pressing either f1 for help, or the fx button to the left of the formula bar at the top of the screen.

 

[sandy666]

Yes, that is the point I want to get to.

use PivotTable
add to DataModel to get DistinctCount
then add Charities to Rows Area and Organistation to Values Area
from Organisation select Value Field Stttings and choose Distinct Count

 

[sandy666]

another way is PowerQuery

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Charities[/td][td=bgcolor:#70AD47]Organistation[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]One[/td][td=bgcolor:#E2EFDA]

4​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Three[/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Two[/td][td=bgcolor:#E2EFDA]

2​

[/td][/tr]
[/table]

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Charities"}, {{"Organistation", each Table.RowCount(Table.Distinct(_)), type number}})
in
    #"Grouped Rows"[/SIZE]