Multiple COUNTIF(S)

[mingandmong]

Hi im using excel 2016
i am having problems with my formula

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]'=COUNTIFS(full_extract!E:E,full_extract!G:G"1",A1)

count value from sheet column E:E, when column G=1 against the data in A1.. i get a syntax error

thankyou
[/FONT]

 

[sandy666]

or you're trying quasi-nested COUNTIFS?
maybe use SUMPRODUCT() instead?
anyway example file with detailed description is a good option

 

[sandy666]

COUNTIFS () does not return a value from a cell, but the number of times a value occurs within the criteria range that matches the criteria.

SUMPRODUCT() will return the sum of values from the defined range according to the criteria. In the extreme case, it will return the value of a single cell. All non-numeric values are treated as 0.

For the above cases, the same size range applies.

You can use the INDEX / MATCH formula to get values from a defined range that matches a defined criterion.

Example:

[Table="width:, class:head"][tr=bgcolor:#000000][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][th]

H​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

1​

[/td][td]

2​

[/td][td][/td][td=bgcolor:#DDEBF7]

1​

[/td][td][/td][td]

5​

[/td][td][/td][td]

1​

[/td][td]=COUNTIFS(A1:A10,--(C1:C10=1))[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

2​

[/td][td]

20​

[/td][td][/td][td]

40​

[/td][td][/td][td]

10​

[/td][td][/td][td=bgcolor:#FFC000]

8​

[/td][td]=SUMPRODUCT(--($A$1:$A$10=--($C$1:$C$10=1)),$E$1:$E$10)[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

3​

[/td][td]

22​

[/td][td][/td][td]

41​

[/td][td][/td][td]

11​

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

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

4​

[/td][td]

24​

[/td][td][/td][td]

42​

[/td][td][/td][td]

12​

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

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

5​

[/td][td]

26​

[/td][td][/td][td]

43​

[/td][td][/td][td]

13​

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

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

6​

[/td][td=bgcolor:#DDEBF7]

1​

[/td][td][/td][td=bgcolor:#DDEBF7]

1​

[/td][td][/td][td=bgcolor:#FFC000]

8​

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

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

7​

[/td][td]

30​

[/td][td][/td][td]

45​

[/td][td][/td][td]

15​

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

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

8​

[/td][td]

32​

[/td][td][/td][td]

46​

[/td][td][/td][td]

16​

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

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

9​

[/td][td]

34​

[/td][td][/td][td]

47​

[/td][td][/td][td]

17​

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

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

10​

[/td][td]

2​

[/td][td][/td][td=bgcolor:#DDEBF7]

1​

[/td][td][/td][td]

7​

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

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

11​

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

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

12​

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

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

13​

[/td][td=bgcolor:#DDEBF7]

1​

[/td][td][/td][td=bgcolor:#DDEBF7]

1​

[/td][td][/td][td=bgcolor:#FFFF00]

5​

[/td][td][/td][td]

3​

[/td][td]=COUNTIFS(A13:A22,--(C13:C22=1))[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

14​

[/td][td]

20​

[/td][td][/td][td]

40​

[/td][td][/td][td]

10​

[/td][td][/td][td=bgcolor:#FFFF00]

20​

[/td][td]=SUMPRODUCT(--($A$13:$A$22=--($C$13:$C$22=1)),$E$13:$E$22)[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

15​

[/td][td]

22​

[/td][td][/td][td]

41​

[/td][td][/td][td]

11​

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

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

16​

[/td][td]

24​

[/td][td][/td][td]

42​

[/td][td][/td][td]

12​

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

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

17​

[/td][td]

26​

[/td][td][/td][td]

43​

[/td][td][/td][td]

13​

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

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

18​

[/td][td=bgcolor:#DDEBF7]

1​

[/td][td][/td][td=bgcolor:#DDEBF7]

1​

[/td][td][/td][td=bgcolor:#FFFF00]

8​

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

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

19​

[/td][td]

30​

[/td][td][/td][td]

45​

[/td][td][/td][td]

15​

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

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

20​

[/td][td]

32​

[/td][td][/td][td]

46​

[/td][td][/td][td]

16​

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

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

21​

[/td][td]

34​

[/td][td][/td][td]

47​

[/td][td][/td][td]

17​

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

[tr=bgcolor:#FFFFFF][td=bgcolor:#000000]

22​

[/td][td=bgcolor:#DDEBF7]

1​

[/td][td][/td][td=bgcolor:#DDEBF7]

1​

[/td][td][/td][td=bgcolor:#FFFF00]

7​

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

in cases above formulas are overcombined

 

[mingandmong]

Hi sandy666
Thankyou for your help, as yousaid i was missing a crieria, i have now moved on wth my SUMIFS
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=SUMIFS(sheet1!$C:$C,sheet1!$G:$G,"3",sheet1!$E:$E,Sheet2!$A3)[/FONT]

 

[sandy666]

i was missing a crieria, i have now moved on wth my SUMIFS
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=SUMIFS(sheet1!$C:$C,sheet1!$G:$G,"3",sheet1!$E:$E,Sheet2!$A3)[/FONT]

and it works for you as you expected?

 

[mingandmong]

Hi sandy666

i have managed to use [FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=COUNTIFS(sheet1!$F:$F,"1",sheet1!$D:$D,Sheet2!$A3) the sheet name and columns have changed but it works..thank you very much[/FONT]

 

[sandy666]

I am glad it works for you

Have a nice day