Formula Help

[Kyle LeFevre]

I'm scratching my head trying to find a "simple" way to write a formula to calculate what I need.

I have a table that has dates, irrelevant info(for this question), and 99 questions (1 per column).

Simply, I'm trying to calculate how many dates have zero answers of "no". I think I may just be over thinking it, but any help would be appreciated!

 

[vrunda]

Pls upload the file.

 

[NARAYANK991]

Hi Kyle ,

Can you clarify if there will be duplicate dates in your dates column ?

If there will not be duplicate dates , then you can use something like the following array formula , to be entered using CTRL SHIFT ENTER :

=ROWS(YES_or_NO)-SUM(IF(MMULT(--(YES_or_NO="NO"),TRANSPOSE(COLUMN(YES_or_NO)^0))>0,1,0))

where YES_or_NO is your range of 99 columns and whatever number of rows.

Narayan

 

[Kyle LeFevre]

I am going to attach a file that gives an example. There could potentially be duplicate dates.

 

[Somendra Misra]

Hi Kyle,

If I understood your requirement correctly, you want:
1. The total count of Dates with no "NO" on that day
2. Want to know the dates when there are no NO. If So, than use below array formulas with Ctrl+Shift+ENter to get the result.

1.

=SUM(IF(MMULT(--(Table1[[Question 1]:[Question 15]]="No"),TRANSPOSE(COLUMN($F$2:$T$2))^0)=0,1,0))

2. In F9 and copy down till you get an error:

=INDEX(Table1[Date],SMALL(IF(--(MMULT(--(Table1[[Question 1]:[Question 15]]="No"),TRANSPOSE(COLUMN($F$2:$T$2))^0)=0),ROW(Table1[Date])-ROW($E$2)+1),ROWS(F$9:F9)))

As far as duplicates are concerned, I did not understood your requirement what you want to do with them, your sample file has 15-Jun as dup. in first one you have a NO but not in second one. So what should be done in such cases.

Regards,

 

[NARAYANK991]

Hi ,

See the uploaded file ; I have used helper cells.

Narayan