Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-26-2012, 01:34 AM
lucnijs lucnijs is offline count how many tims a certain value occurs Mac OS X count how many tims a certain value occurs Office 2008 for Mac
Novice
count how many tims a certain value occurs
 
Join Date: Mar 2012
Posts: 3
lucnijs is on a distinguished road
Default count how many tims a certain value occurs

Hi!

I have a spreadsheet for the amount of times students practised their instrument and for the help they get from their parents

so I have 2 columns per day, one for the amount of time practised and one to indicate if and by whom they were helped.
e.g.
F = Monday_practiceTime
G = Monday_helpedBy
H = Tuesday-practiceTime
I = Tuesday_helpedBy
J = Wednesday_practiceTime
K = Wednesday_helpedBy
...

I now want to see how many days they practised a week.
See I was thinking to count the cells with a number greater than 0.
I used COUNTIF:

COUNTIF(F2+H2+J2+L2+N2+P2+R2, ">0")

But I get an error message saying that the range is invalid.

How can I do this right?


I use Excel for mac 2008

Thank you!

Luc
Reply With Quote
  #2  
Old 03-26-2012, 02:23 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline count how many tims a certain value occurs Windows 7 64bit count how many tims a certain value occurs Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,920
Pecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond repute
Default

Perhaps
Code:
=SUMPRODUCT(--(MOD(COLUMN(F2:R2),2)=0),--(F2:R2>0))
__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #3  
Old 03-26-2012, 02:29 AM
lucnijs lucnijs is offline count how many tims a certain value occurs Mac OS X count how many tims a certain value occurs Office 2008 for Mac
Novice
count how many tims a certain value occurs
 
Join Date: Mar 2012
Posts: 3
lucnijs is on a distinguished road
Default

thank you for your answer.
I've tried but I still get an error message...
Reply With Quote
  #4  
Old 03-26-2012, 09:29 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline count how many tims a certain value occurs Windows 7 64bit count how many tims a certain value occurs Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,920
Pecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond repute
Default

Quote:
Originally Posted by lucnijs View Post
thank you for your answer.
I've tried but I still get an error message...
Could you please tell waht the error message was?
Is it related to the fact you are using XL for MAC ?
__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #5  
Old 03-26-2012, 02:45 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline count how many tims a certain value occurs Windows 7 32bit count how many tims a certain value occurs Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 369
Colin Legg will become famous soon enough
Default

I believe the COUNTIFS function is available in Excel 2008, so something like this should work:

=COUNTIFS(F1:T1,"*practiceTime",F2:T2,">0")


We're applying two conditions with this formula. The first condition is that the cell in the header row F1:T1 must contain the word practiceTime. The second condition is that the cells in the data row F2:T2 must contain a value greater than 0.
Reply With Quote
  #6  
Old 03-26-2012, 02:57 AM
lucnijs lucnijs is offline count how many tims a certain value occurs Mac OS X count how many tims a certain value occurs Office 2008 for Mac
Novice
count how many tims a certain value occurs
 
Join Date: Mar 2012
Posts: 3
lucnijs is on a distinguished road
Default

Thank you, Colin!
Unfortunately I get an error message again, saying that criteria_range1 is invalid.

luc
Reply With Quote
  #7  
Old 03-26-2012, 03:06 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline count how many tims a certain value occurs Windows 7 32bit count how many tims a certain value occurs Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 369
Colin Legg will become famous soon enough
Default

Quote:
Unfortunately I get an error message again, saying that criteria_range1 is invalid.
Did you make any changes to the formula I posted? I've attached example which uses it.
Attached Files
File Type: xlsx Book1.xlsx (10.6 KB, 15 views)
Reply With Quote
Reply

Tags
countif



Similar Threads
Thread Thread Starter Forum Replies Last Post
Use of MIN and count function together khokababu Excel 0 02-22-2012 09:40 AM
Count If Formula beb1227 Excel 10 06-19-2011 11:19 AM
count how many tims a certain value occurs Count By Color beb1227 Excel 4 06-15-2011 08:46 AM
count how many tims a certain value occurs Count in Excel zanat0s Excel 3 06-09-2011 10:53 AM
How to Count from Different Cells? Shahzad Excel 3 06-26-2010 01:06 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 07:53 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft