Empty cells and countif

**pjotr** · 11-23-2011, 03:12 AM

Unknown amount of participants (1-50) will give values 1-5 in two polls (kysely), from poll A we need only values 1-4.
In Sheet2 people make same poll later.
in Sheet3 it subtracts Sheet2 results from Sheet1.
Then it should pick up how many gave >=1 in poll b than poll a, this works fine. In my example 2.
Then it should pick up how many gave <1 in poll b than poll a, but excludes if somebody gave 5 in poll a. In my example 28, should be 6.
It won´t exclude fives from sheet1. I tried many different formulas, but couldn´t get it working.
Is there easy way to get rid of those extra zeros? i used paste link in 2 first sheets and was able to hide zeros from Excel options, but i need empty cells B2:B51 in Sheet3.

**TMShucks** · 11-23-2011, 03:29 AM

A2: =IF(Taul1!A2="","",Taul1!A2)
B2: =IF(AND(Taul2!B2="",Taul1!B2=""),"",Taul2!B2-Taul1!B2)

Not sure if this gives the result you are looking for:

=SUMPRODUCT(--(B2:B51<>""),--(B2:B51<>0),--(B2:B51<1))-IF(Taul1!B2:B51<=4,)

Regards

**pike** · 11-23-2011, 03:30 AM

Hi pjotr

Welcome to the forum

you can add an If function to check for blank cells

=IF(Taul1!A6<>"",Taul1!A6,"")

**pjotr** · 12-01-2011, 02:32 AM

Originally Posted by TMShucks

Not sure if this gives the result you are looking for:

=SUMPRODUCT(--(B2:B51<>""),--(B2:B51<>0),--(B2:B51<1))-IF(Taul1!B2:B51<=4,)

This works, but still includes fives from sheet1. -IF(Taul1!B2:B51<=4,) doesn't do anything.
For example, I have 1,1,1,5,5,5 in first poll and 2,2,2,3,3,3 in second. Formula should give 0, but it count's fives and gives 3 (3-5= <1).

Thanks for the help so far.

**pjotr** · 12-09-2011, 12:24 AM

Still need help!!
How can I add (Sheet1!B4:B53<=4;) to this =IF(AND(Sheet2!B13="";Sheet1!B13="");"";Sheet2!B13-Sheet1!B13)
Have tried everything, but it just doesn't work.

**dilipandey** · 12-10-2011, 03:48 AM

Hi pjotr,

It can be simply added like:-

=IF(AND(Sheet2!B13="",Sheet1!B13="",Sheet1!B4:B53<=4),"",Sheet2!B13-Sheet1!B13)

I would suggest you to attach your file which will help forum to help you better.

Regards,
DILIPandey
< click on below 'Star' if this helps >

**pjotr** · 12-12-2011, 02:44 AM

I have already tried that, but didn't work. Sheet1!D4:D53<=4 is also in Sheet3!D5 but it doesn't do anything.
D7 and D20 should be empty in Sheet3, because of 5s in Sheet1. Only important result I need, is in Sheet3!D3:M3.
In current situation, D3 should be 75.

**dilipandey** · 12-16-2011, 01:36 PM

Hi pjotr,

I am getting an error while opening this file that "converter failed to open the file".
Try re-attaching the file.

Regards,
DILIPandey

<click on below 'star' if this helps>

**pjotr** · 12-19-2011, 02:18 AM

I think it's because of language settings.
I translated it to english, i hope you can open it now.
Formulas are not working for me anymore, but i hope you can see it correctly.

**dilipandey** · 12-19-2011, 07:15 AM

Hi pjotr,

I see all #NAME in the excel and could'nt understand it further.
I believe there is some language issue with my Excel.

Regards,
DILIPandey

<click on below 'star' if this helps>