Countifs function

[Vera1234]

Hi,

I need some help with my Countifs function. This is the formula I have so far.

=ROUND(SUMPRODUCT(COUNTIFS(INDIRECT(""&$A$20:$A$23&"!$F:$F"),"=0",INDIRECT(""&$A$20:$A$23&"!$G:$G"),"=0",INDIRECT(""&$A$20:$A$23&"!$H:$H"),"=0",INDIRECT(""&$A$20:$A$23&"!$B:$B"),">="&$D$6,INDIRECT(""&$A$20:$A$23&"!$B:$B"),"<"&$E$6))/SUMPRODUCT(COUNTIFS(INDIRECT(""&$A$20:$A$23&"!$F:$F"),"<>",INDIRECT(""&$A$20:$A$23&"!$G:$G"),"<>",INDIRECT(""&$A$20:$A$23&"!$H:$H"),"<>",INDIRECT(""&$A$20:$A$23&"!$B:$B"),">="&$D$6,INDIRECT(""&$A$20:$A$23&"!$B:$B"),"<"&$E$6)),2)*100

WHAT I NEED HELP WITH: I want to count how many times the value 0 appears in the columns F, G, and H, TOTAL. Right now the formula is counting how many times 0 appears as a value in each row in ALL three columns. It only counts if 0 appears in F,G,and H, in a particular row. So I want it to count it if there is a 0 in, for example, Row 1 and Column F, even if there is a 1 or 2 in Row 1, Columns G/H.

I know that a + is supposed to kind of act as an "or" in this case, but no matter what I type, it doesn't seem to work. Can anyone help?

Thank you so much!

[FDibbins]

Hi, welcome to the forum \

That us a very complex looking formula there, and it will be hard to break it apart and test, withouit seeing a sample of what you are using it on, so can you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like?
To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[JohnTopley]

For COUNTIFS you will need (as an example)

=COUNTIFS(F1:F30,0)+COUNTIFS(G1:G30,0)+COUNTIFS(H1:H30,0) OR condition

this will not work

=COUNTIFS(F1:F30,0,G1:G30,0,H1:H30,0) AND condition which is what you have in your formula.

[Vera1234]

Hi, thanks for replying! I attached a sample workbook to my initial post just now, is that what you are looking for? the formula is located in B10 of the sheet named REPORT_GR (draft). Sheets F1-F4 are where I am drawing the data from.

[Vera1234]

Hi John, thanks for your reply. Yes, I tried to use the OR condition, but for some reason, it is not working out. I might be getting things wrong because they are indirect formulas.

[JohnTopley]

This works ...

=SUMPRODUCT(COUNTIFS(INDIRECT("'"&$A$20:$A$21&"'!$F:$F"),0,INDIRECT("'"&$A$20:$A$21&"'!$B:$B"),">="&$D$6,INDIRECT("'"&$A$20:$A$21&"'!$B:$B"),"<"&$E$6)+COUNTIFS(INDIRECT("'"&$A$20:$A$21&"'!$G:$G"),0,INDIRECT("'"&$A$20:$A$21&"'!$B:$B"),">="&$D$6,INDIRECT("'"&$A$20:$A$21&"'!$B:$B"),"<"&$E$6)+COUNTIFS(INDIRECT("'"&$A$20:$A$21&"'!$H:$H"),0,INDIRECT("'"&$A$20:$A$21&"'!$B:$B"),">="&$D$6,INDIRECT("'"&$A$20:$A$21&"'!$B:$B"),"<"&$E$6))

[Vera1234]

John, thank you so much!! I really appreciate your help. It is working great One last thing, for anyone willing to help, the values in my chart are showing up as numbers right now, is there a way to convert them to percentages? So, each column in the chart (1 through 7) adds up to 18 values, but I want them to add up to 100% instead. For example, Column 1 in my chart (Column B) has 7 instances of "No Improvement" out of 18, but I want it to say "39%" instead, and so forth. I'm attaching a new workbook sample with the chart fixed with John's formula.

[JohnTopley]

Try this ....

=sumproduct(countifs(indirect("'"&$a$20:$a$21&"'!$f:$f"),0,indirect("'"&$a$20:$a$21&"'!$b:$b"),">="&$d$6,indirect("'"&$a$20:$a$21&"'!$b:$b"),"<"&$e$6)+countifs(indirect("'"&$a$20:$a$21&"'!$g:$g"),0,indirect("'"&$a$20:$a$21&"'!$b:$b"),">="&$d$6,indirect("'"&$a$20:$a$21&"'!$b:$b"),"<"&$e$6)+countifs(indirect("'"&$a$20:$a$21&"'!$h:$h"),0,indirect("'"&$a$20:$a$21&"'!$b:$b"),">="&$d$6,indirect("'"&$a$20:$a$21&"'!$b:$b"),"<"&$e$6))/sumproduct(countifs(indirect("'"&$a$20:$a$21&"'!$f:$f"),">=0",indirect("'"&$a$20:$a$21&"'!$b:$b"),">="&$d$6,indirect("'"&$a$20:$a$21&"'!$b:$b"),"<"&$e$6)+countifs(indirect("'"&$a$20:$a$21&"'!$g:$g"),">=0",indirect("'"&$a$20:$a$21&"'!$b:$b"),">="&$d$6,indirect("'"&$a$20:$a$21&"'!$b:$b"),"<"&$e$6)+countifs(indirect("'"&$a$20:$a$21&"'!$h:$h"),">=0",indirect("'"&$a$20:$a$21&"'!$b:$b"),">="&$d$6,indirect("'"&$a$20:$a$21&"'!$b:$b"),"<"&$e$6))


(You could replace the ">=0" with "<>")

[Vera1234]

Thank you so much, John!!! Everything is working perfectly now. Thanks for your time and effort.

[JohnTopley]

If all is OK, could you please mark thread as solved ("Thread Tools" at top of first post).

Appreciate the feedback.