Can we nest an IF inside a COUNTIFS in Excel? -

-

i have been working on attendance sheet , trying make monthly reports automatic. have asked my previous question on same issue , got idea accomplish task.

but have stuck @ 1 place. have below formula:

=countifs(c5:c27,">0", e5:e27,"g", f5:f27,"cat1")

the value in cell "c" in above coming below formula (in cell "c")

=if((countif(g5:ak5,"p"))>0,1,0)

i had add column ("c") supply input fist formula. question - "can merge if function inside countifs result in 1 go , eliminate use of column (column c)"?

to perform these cell reference acrobatics need switch array formula. array formulas chew calculation cycles logarithmically practise narrow referenced ranges minimum. 'helper' column such you've used in column c can reduce calculation cycles , make worksheet more 'user friendly'.

a countifs function requires ranges being examined not same size same shape. looking @ g5:ak5 not same looking @ e5:e35 though contain same number of cells¹.

in sample data below, formula in a1 , uses 'helper column' c. array formula in a2 , not consider column c ahough incorporated logic.

        gserg's comment wrong.

the array formula in a2 is:

=sum(if(e5:e27 = "g", if(f5:f27 = "cat1", sign(countifs(offset($g$5, row($1:$23)-1, 1, 1, 31), $i2)))))

array formulas need finalized ctrl+shift+enter↵. once entered first cell correctly, can filled or copied down or right other formula.

¹some functions not accept welcome cell ranges same number of calls transposed. offsetting or staggering ranges option if cell ranges same size. in difficult cases transpose function can helpful.


Comments

Post a Comment

Popular posts from this blog

php - Admin SDK -- get information about the group -

-
please, help. https://www.googleapis.com/admin/directory/v1/groups/106274944935548788283 sending request obtain information group id 1062 ... here link method in google https://developers.google.com/admin-sdk/directory/v1/guides/manage-groups#get_group in response error - 401 unauthorized. oauth authenticate via user has received rights necessary request, included in console admin sdk. authorization successful, there token. not can not understand. if not hard - response. requested information: file_get_contents("https://www.googleapis.com/admin/directory/v1/groups/1062749‌​44935548788283?key=aizasybruhiomdf9nlx9lx3imxwesqmcrnhh4l4"); id group - "106274944935548788283" key applications in case - "aizasybruhiomdf9nlx9lx3imxwesqmcrnhh4l4" error: failed open stream: http request failed! http/1.0 401 unauthorized
Read more

dns - How To Use Custom Nameserver On Free Cloudflare? -

-
i have problem. i own small hosting company. trying add cloudflare site free cdn. problem when sign have change nameservers nameservers. have nameserver dns set through godaddy, how add them cloudflare? main site uses company's nameservers whenever add them site goes offline. when go cloudflare's dns records click on dropdown, click ns, , there's 2 boxes 'name' , 'nameserver'. mean input 'ns1' 'name' , 'ns1.domain.com' 'nameserver'? input server's ip addresses? is there way of accomplishing free version of cloudflare? that impossible. option have custom name servers available on business , enterprise. see https://www.cloudflare.com/plans . also, might mistaken. must change name servers in domain register settings, not on cloudflare.
Read more

Python Error - TypeError: input expected at most 1 arguments, got 3 -

-
can explain why can not use your_name in goal variable? my_name = "bryson" my_age = 29 your_name = input ("what name? ") your_age = input ("what age? ") print ("my name is", my_name,", , am", my_age, "years old.") print ("your name is", your_name,", , are", your_age,".") print("thank buying book,", your_name,"!") goal = input ("what favorite part of book,", your_name, "?") print("awesome!") the error is: goal = input ("what favorite part of book,", your_name, "?") typeerror: input expected @ 1 arguments, got 3 you got error because in fact gave 3 arguments input function when expecting 1 (namely, string prompt). in input ("what favorite part of book,", your_name, "?") ---------------------------------------- , ---------, --- the underlined parts comma-separated arguments: st...
Read more