Nested CountIF & SUMIF?

[canthony24]

Hello! I'm racking my brain around trying to nest these two formulas...if that's even the right way to do it. I'm trying to show the total number of blank cells for only the KVI items, not all items.

So I have this formula below to pull the total number of products with a KVI score over .001. In this case it's 201 items.

=(COUNTIF(INDEX(FinalOutPut!A8:ZZ10000,,MATCH("KVI Item",FinalOutPut!A7:ZZ7,0)),">.001"))

I have this other formula to pull the total number of blank cells within a certain attribute. This looks at the total file and it's 261 items.

=SUM($C$6,-(COUNTA(FinalOutPut!$AE$8:$AE$10000)))

The file is dynamic on attributes and can change depending on the category. I know for the specific example above, I need to get to 27.

Total file has 1,214 items
KVI items = 201
Missing cells for particular attribute = 261

Any help is appreciated!

 

[KRice]

Several details are missing from your description. A small sample illustrating what you want would be helpful. You describe this objective:

I'm trying to show the total number of blank cells for only the KVI items, not all items.

What is meant by "KVI items"? You search for a column (heading?) named "KVI Item" with MATCH("KVI Item",FinalOutPut!A7:ZZ7,0). For discussion purposes, let's say we find "KVI Item" in cell D7. So in that case, what specifically are KVI Items?...is it the set of rows where column D is not blank?...is it the set of rows where column D has a value > 0.001...it is something else?

If you want the count of items in the "KVI Item" column that are greater than 0.001, then the 1st formula appears to be okay.

The answer to these questions will help me/others understand what to suggest for the other count...the count of blanks. Here is what I am imagining...you want the count of blanks in column AE for any row where the value in the KVI column is >0.001....but that might be a bad assumption.

MrExcel_20220613.xlsx
	A	B	C	D	E	F	G																								AE
1	count KVI > 0.001	5
2	count AE blanks where KVI > 0.001	2
3
4
5
6
7	row for matching text "KVI Item"	alpha	beta	KVI Item	delta	epsilon																									blanks here?
8				0																											a
9																															c
10				0.0001
11				0.0011																											1
12				0.1
13				0.001
14				0.2																											d
15				0.15
16				0.12																											e
Sheet2

Cell Formulas
Range	Formula
B1	B1	=COUNTIF(INDEX(A8:ZZ10000,,MATCH("KVI Item",A7:ZZ7,0)),">.001")
B2	B2	=COUNTIFS(INDEX(A8:ZZ10000,,MATCH("KVI Item",A7:ZZ7,0)),">.001",AE8:AE10000,"")

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)