countifs

[Chuck_Finnegan]

Hello all

I have used a countifs formula =COUNTIFS('[IP Data Entry.xls]Battery Data'!$B:$B,B19,'[IP Data Entry.xls]Battery Data'!$M:$M,$B$5) excel 2010, however am unsure how I would be able to use this at work tomorrow when using excel 2003.

Is their anyway I will be able to use an array of some sort etc so this will work??

 

[Aladin Akyurek]

That will not work on Excel 2003. The function has become available with Excel 2007. Moreover, it won't work either if IP Data Entry.xls is closed.

Try instead an array-processing formula like:

=SUMPRODUCT(
    --('[IP Data Entry.xls]Battery Data'!$B$2:$B$400=B19),
    --('[IP Data Entry.xls]Battery Data'!$M$2:$M$400=$B$5))

Also, as you can see, the whole column references are not allowed on 2003.

 

[Chuck_Finnegan]

Thankyou very much for your help. This has worked perfectly.

 

[Aladin Akyurek]

Thankyou very much for your help. This has worked perfectly.

Great. Thanks for the feedback.

 

[Chuck_Finnegan]

Hello again

=SUMPRODUCT(--('[IP Data Entry.xls]Battery Data'!$B$2:$B$400=$B$19),--('[IP Data Entry.xls]Battery Data'!$M$2:$M$400=$B$5))

I have tried dragging this formula across from cell "B20:P20" and have changed $B$19 to B19 so it is no longer an absolute.. It is not showing the any results. I have tried breaking the formula down however am still stumped. (I am very new to excel.. so am still trying to learn the basics)

My apologies for coming back to this query

Thankyou

 

[Aladin Akyurek]

Hello again

=SUMPRODUCT(--('[IP Data Entry.xls]Battery Data'!$B$2:$B$400=$B$19),--('[IP Data Entry.xls]Battery Data'!$M$2:$M$400=$B$5))

I have tried dragging this formula across from cell "B20:P20" and have changed $B$19 to B19 so it is no longer an absolute.. It is not showing the any results. I have tried breaking the formula down however am still stumped. (I am very new to excel.. so am still trying to learn the basics)

My apologies for coming back to this query

Thankyou

If this is B20, what must look like in C20?

 

[Chuck_Finnegan]

If this is B20, what must look like in C20?

It should look like this (atleast this is how I worked it out to be);

=SUMPRODUCT(--('[IP Data Entry.xls]Battery Data'!$B$2:$B$400=C19),--('[IP Data Entry.xls]Battery Data'!$M$2:$M$400=$B$5))

 

[Aladin Akyurek]

It should look like this (atleast this is how I worked it out to be);

=SUMPRODUCT(--('[IP Data Entry.xls]Battery Data'!$B$2:$B$400=C19),--('[IP Data Entry.xls]Battery Data'!$M$2:$M$400=$B$5))

Enter in B20 and copy across:

=SUMPRODUCT(
    --('[IP Data Entry.xls]Battery Data'!$B$2:$B$400=B$19),
    --('[IP Data Entry.xls]Battery Data'!$M$2:$M$400=$B$5))

 

[Chuck_Finnegan]

Enter in B20 and copy across:

=SUMPRODUCT(
   --('[IP Data Entry.xls]Battery Data'!$B$2:$B$400=B$19),
   --('[IP Data Entry.xls]Battery Data'!$M$2:$M$400=$B$5))

I have done this, unfortunately still not yielding any results

 

[Aladin Akyurek]

I have done this, unfortunately still not yielding any results

What result did you expect and what did you get?