• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel Count Problem

D

DIGBY

New Member
I trying to find a solution to count the number of times a specific number is entered in the even rows of a column. The actual range of columns is row 18 to 205, e.g. C18:C205. The even cells in the column are entered with a number in the range of 1 to 10 but never zero. There are empty cells, but these are always at the end of the range, e.g. the numbers are entered sequentially commencing at C18. Either a date in the format of DD/MM/YYYY is entered into the corresponding cell in column B, e.g. B18 when entering the number into cell C18 or an alternative reference, e.g. LCE or a name or similar.

There are also numbers in the odd row of each pair of cells, e.g. C19 has a number that is the range of zero to 6. These should never be included in the calculation.

In the example attached the objective is to establish the number of occasions the number 4 is entered. The solution should only count the number of times “4” or another number from 1 to 10 is recorded in the even row of the column if there is a date in the corresponding cell in column B.

I have previously used the formula COUNTIF, e.g. =COUNTIF(C18,”4”)+COUNTIF(C20,”4”) etc. but this does not accommodate even rows where the number entered should be ignored where there is not date related in column B. I have incorporated a date into other solutions using SUMIFS, but this does not seem possible in this case?

Thanks in advance for any solution that might be available.
 

Attachments

  • Book1.xlsx
    10.4 KB · Views: 3
Code: 
=COUNTIFS($B18:$B205,"<>",C18:C205,4)
copied across.
 
Last edited:
Thank you, appreciated, this solution suggested does work & counts the number of the specified digit in the even cells of the row but does not take account of the date. The solution should count but only when a date in the format of DD/MM/YYYY is in the corresponding column B & ignore the digit if the cell in column B is filled with another reference other than the date, e.g. ABC. The even & odd cells will always be blank if column B is blank.
 
Dates can be a little difficult for Excel to identify robustly but maybe:
=COUNTIFS($B18:$B205,">" & DATE(2020,1,1),C18:C205,4)
or shorter:
=COUNTIFS($B18:$B205,">43831",C18:C205,4)
will both include the count if the dates are after 1/Jan/2020 (but will also count if there's a plain number larger than 43831).

ps. your formulae showed you included references other than a date!
 
Thank you, again much appreciated, I've used =COUNTIFS($B18:$B205,">" & DATE(2024,1,1),C18:C205,4) with the small modification to the date & this works. Sorry for the earlier confusion, but my problem is resolved & I'm grateful for the quick response.
 
My apology for asking a further related question.
I currently SUM a column where the cells are either zero or have a value, 1, 2, or 3 etc. but does not include the date component.
Currently I have =SUM(AR18+AR20, etc.,) & =SUM(AR19+AR21, etc.,).

I would like to sum the odd & even rows separately but only if there is date in column B, [DD/MM/YYYY] e.g. the value in the cell is not included in the sum if there is no date component or the entry in column B is 43831 for example.

The range is AR18 to AR205 in each column.

I have configured =SUMIFS(AR$18:AR$204,$B18:$B204,">"&DATE(2024,1,1)) & =SUMIFS(AR$18:AR$205,$B18:$B205,">"&DATE(2024,1,1))
Both return the correct value for the even rows.

=SUMIFS(AR19:AR205,$B18:$B205,">"&DATE(2024,1,1))
Returns #VALUE

Can you assist me with a solution to sum the odd cells in the column?

Thanks in advance.
 
=SUMIFS(AR19:AR205,$B18:$B204,">"&DATE(2024,1,1))
The the two ranges need to be the same size.
 
Thank you, sorted, it's appreciated. One last general question, I notice that =COUNTIFS(DATA2024!$B18:DATA2024!$B205,">"& DATE(2024,1,1),DATA2024!I18:DATA2024!I205,8) [which is your solution with my modification for the total to be on another sheet] & works fine that you did not enclose the 8 thus "8". I have thought that the "" were necessary?
 
Excel will implicitly convert numeric to strings and vice versa according to context:
=8 + "1"
="8" + 1
="8" + "1"
will all return the number 9

="8" & 1
=8 & "1"
=8 & 1
will all return the string "81"

In the formula you use, it makes no difference, so why use quotes at all? The fact that you're actually looking for numbers means that really you should use numbers too!
 
You must log in or register to reply here.
Back
Top