Adding Conditions to change the range of cells used in a countif formula

**tcusack** · 08-22-2013, 11:52 AM

Hi All,
Any help on this issue would be great!
Basically I have a countif formula that is counting the number of log-ins in column B that happen within set time ranges in columns D and E and is returning a value in comun F for that day of the year (01/08/2012).

My issue is I want to copy the formula into column G for the 02/08/2012 but only want the countif formula to now look at the rows that are dated the 02/08/2012 and not the 01/08/2012 and so on as i need do it for an entire year.

I don't think I'm explaining myself very well so I have attached an example of my worksheet so hopefully it makes it clearer what it is I am trying to do.

Again any help would be much appreciated!

**NBVC** · 08-22-2013, 12:01 PM

Try:

=COUNTIFS($B$3:$B$556,">="&$D3,$B$3:$B$556,"<="&$E3,$A$3:$A$556,F$1)

copied across and down

need to change range sizes to get the results, though

**tcusack** · 08-22-2013, 01:37 PM

Originally Posted by NBVC

Try:

=COUNTIFS($B$3:$B$556,">="&$D3,$B$3:$B$556,"<="&$E3,$A$3:$A$556,F$1)

copied across and down

need to change range sizes to get the results, though

Great help, thanks for that, I changed the range sizes and it works! The one other thing is that if I do a manual count on the 01/08/12 the total log-ins is 554 but the total per column F is 549 which may be something to do with my original formula.

Do you know is there anything else I need to change?

Thaks again.

**NBVC** · 08-22-2013, 02:02 PM

It has to do with precision when manipulating multiple decimal numbers like time. You are using formula in D4 like =E3+TIME(0,$F$1,0) copied down... that causes inaccuracies when comparing to fixed entry time

What I would do is insert a new column between column B and C, then in C3 enter formula:

=ROUND(B3,6)

copied down

in the new G2, enter:

=COUNTIFS($C:$C,">="&ROUND($E3,6),$C:$C,"<="&ROUND($F3,6),$A:$A,G$1)

copied down. The sum should now be 554. Copy across. I used the Round() function to "control" the result to 6 decimals

**tcusack** · 08-22-2013, 02:12 PM

Originally Posted by NBVC

It has to do with precision when manipulating multiple decimal numbers like time. You are using formula in D4 like =E3+TIME(0,$F$1,0) copied down... that causes inaccuracies when comparing to fixed entry time

What I would do is insert a new column between column B and C, then in C3 enter formula:

=ROUND(B3,6)

copied down

in the new G2, enter:

=COUNTIFS($C:$C,">="&ROUND($E3,6),$C:$C,"<="&ROUND($F3,6),$A:$A,G$1)

copied down. The sum should now be 554. Copy across. I used the Round() function to "control" the result to 6 decimals

That worked perfect, thanks for the help!

Adding Conditions to change the range of cells used in a countif formula

LinkBack

Thread Tools

Rate This Thread

Display

Adding Conditions to change the range of cells used in a countif formula

Re: Adding Conditions to change the range of cells used in a countif formula

Re: Adding Conditions to change the range of cells used in a countif formula

Re: Adding Conditions to change the range of cells used in a countif formula

Re: Adding Conditions to change the range of cells used in a countif formula

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Countif a range of cells is MAX in their row(array formula)

Does COUNTIF work if adding non-sequential cells i.e. not a range

[SOLVED] VBA Copy a range of cells values using the range of cells in a formula (which will change)

VBA Copy a range of cells values using the range of cells in a formula (which will change)

Adding a simple formula to a range of cells

Bookmarks

Bookmarks

Posting Permissions