Need help with COUNTIF (?) formula

[JasonTheSparky]

Afternoon all,

Looking for help writing a formula for a ledger spreadsheet that counts multiple entries for a single date (counts as 1 per date group) but only counts the entry if there is an associated time with the same row. I have gotten the COUTNIF function to work perfectly, but only counting multiple day entries as a single count.

What I have currently:

Please Login or Register  to view this content.

2021-04-01 13_41_37-Time_Ledger_2021_v2 - Excel.png

In the attached picture, The cells in orange (column C) will count as 1 day in my spreadsheet, but I am looking for only the top one to count as 1 because there is a time value associated with it (column H, in orange).

I have tried variations of:

Please Login or Register  to view this content.

but it returns a decimal value instead of a whole number. Any help would be greatly appreciated!!!

[JohnTopley]

Please read the yellow banner at the top the page on how to attach an Excel file.

[JasonTheSparky]

Sample sheet attached. Formula is in cell C5. Thank you, I Appreciate the help!

[CARACALLA]

C5=SUM(IF(FREQUENCY(IF(C8:C100<>"",C8:C100),C8:C100),1))

control+shift+enter

[JasonTheSparky]

Thank you for the quick response! After rereading my original post, I think my original description may have been confusing, my sincerest apologies. My current formula groups multiple rows with the same date and counts them as 1 and it looks like your suggested formula accomplishes the same thing.


What I am looking for help with is writing a formula that will count a row that has a date in a cell in column C and in the same row an associated time in the cell in column H. The rows that have the same date in column C will still only count as 1. The intention is there will be multiple rows with entries with the same date with time logged for that day (count as 1 day worked). But, there will occasionally be an entry with a date, but no time associated - those days I do not want to count as a day worked - only count the entries with a date that have a time with them.

Thank you all for looking!

[AliGW]

So where have you mocked up what you want? I can only see what you don't appear to want in the workbook. What is the RESULT (value) you are looking for and where should it appear?

[JasonTheSparky]

AliGW,

Thank you for your help!


In the attached sample workbook, there are 4 groups of dates listed (1-Apr-2021, 2-Apr-2021, 3-Apr-2021, and 4-Apr-2021). The formula in cell C5 returns a "4", as it is counting the number of days listed in the workbook. I am looking for it to return a "2" instead because while there are 4 days listed, there are only 2 days that have any time associated with them (listed in column H).

[AliGW]

Try this:

=SUM(IF(H8:H200<>"",1/COUNTIF(C8:C200, C8:C200), 0))

[JasonTheSparky]

AliGW,

I appreciate the suggestion, we are definitely getting warmer! It does not quite work as expected though; with that formula, if there is only 1 entry for a given day with time associated with it, the result will include that day. If there are 2 entries for a given day, but only one of the entries has time associated with it, the result will reduce the count by 1.

In the end, I am looking for at least 1 entry for any given date that includes time for that day to be added to the "Days Worked This Month" result (cell C5). For example: If there is one entry for 2-Apr-2021 with an associated time (column H), then it counts as 1; if there are 20 entries for 2-Apr-2021 but only 1 entry with associated time, then it still counts as 1; if there is 1 or more entries for 2-Apr-2021 but none have associated time with them, then it does not count at all.

[CARACALLA]

C5=SUM(IF(FREQUENCY(IF(ISNUMBER(H8:H200),C8:C200),C8:C200),1))

Control+shift+enter

[Kirkules]

I know you can do it with an array, but this is one way of doing it.

In Column A:

Please Login or Register  to view this content.

C5:

Please Login or Register  to view this content.

(-1 because I used 0 as false and it shows as a unique value)

[bebo021999]

May be:

=SUM(IF(H8:H200<>"",1/COUNTIFS(H8:H200,"<>",C8:C200,C8:C200),0))

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

[AliGW]

As the OP has MS365, there's no need to enter any formula as an array formula.

[bebo021999]

It may be benefits for whom do not have MS365

[JasonTheSparky]

bebo021999,

I tried your suggested formula and it appears to work perfectly for what I wanted, thank you!!!



Thank you guys for your help!

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of ALL those who helped.