Count unique rows in conditional range via formula

[Egor.Kochetoff]

I have a table with the following data:

|  <A>  |  <B>  |
| Event | Date  |
| Evt1  | 1 Oct |
| Evt1  | 1 Oct |
| Evt2  | 1 Oct |
| Evt1  | 2 Oct |

I wish to create a formula which will return how many days there were when event1 occured at least once. I.e. count how many unique values are there in the column <B> in such rows, where the value in <A> is “Evt1”. I’m not interested in this information for any other events. Which functions should I use? Is it possible at all without a macro?

In my particular case this formula must return 2 (there were two distinct days: 1 Oct and 2 Oct).

BTW I’ve found something that looks relevant but failed to adapt it to my case: MSOffice help article.

I’d prefer not using a macro since it’s more portable across LO/MS Office and since it does not issue a warning about embedded macros.

[ROSt53]

@Egor.Kochetoff - How do you determine the combination of “Evt1” and “1 Oct”?

[Rugslug]

Make sure data is clean/consistant. This might work.

Your values would need to be sorted by date 1st; Event 2nd.

The following formula goes in column C:

=IF(A2<>A1,1,IF(AND(A2<>A1,B2<>B1),1,IF(AND(A2=A1,B2<>B1),1,0)))

Then Sum Up the Values in Column C.

[mariosv]

I think the issue for adapt is that you have two conditions.

Please take a look if the sample with several ways to do it.

SumConditionally_SumIf_SumIfs_Sumproduct_PivoTable-Getpivotdata.ods

[Lupp]

I might suggest using one of two ways I took in account, the one using a rather elaborate formula, the otherone being simple and clearly structured. The more subtle solution I found some time described in the OOo forum in a somewhat different context. It was presented there by “MrProgrammer” but I do not know the original source.

Inspect the attached demonstration for more details, please. ask41236AnotherCountUnique001.ods