countif unique values using multiple criteria

James Published at Dev

James

I have read a bunch of other posts, but havent fount what I need. I need to do a countif on 2 criteria, but only count the unique "EventIds" in this case that meet it. currently it counts the total number that meet the criteria

EventID     DR Ref(F)   Number Locations (G)
110000018   1           13
110000018   2           2
110000018   3           8
110000018   4           5
110000252   1           3
110000252   2           3
110000354   1           1
110000366   1           2
110000366   3           1

I have the data above and am trying to display it in a matrix below, currently I am using a countif with F being the "DR Ref" column, "AB" being DR1 a

=COUNTIFS($F$4:$F$12,">="&AB$2,$G$4:$G$12,">="&$Y4)

this is the current output...

            DR
Locat    1(AB)  2   3
> 1        9    5   3
> 2        7    4   2
> 3        5    3   2
> 4        3    2   2
> 5        3    2   2
> 6        2    1   1
> 7        2    1   1
> 8        2    1   1
> 9        1    0   0
> 10       1    0   0
> 11       1    0   0
> 12       1    0   0
> 13       1    0   0

Desired output

this is the wanted output...

            DR
Locat    1(AB)  2   3
> 1        4    3   2
> 2        3    2   1
> 3        2    2   1
> 4        1    1   1
> 5        1    1   1
> 6        1    1   1
> 7        1    1   1
> 8        1    1   1
> 9        1    0   0
> 10       1    0   0
> 11       1    0   0
> 12       1    0   0
> 13       1    0   0

sacse

Use the following formula:

=SUM(IF(($F$4:$F$12>=AB$2)*($G$4:$G$12>=$Y4),1/COUNTIFS($E$4:$E$12,$E$4:$E$12,$F$4:$F$12,">="&AB$2,$G$4:$G$12,">="&$Y4),0))

and press ctrl+shift+enter

Check the code here.

but I have to say that you phrased the question in a very typical manner.

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2021-05-7

Comments

0 comments

Sum unique values with multiple criteria

COUNTIF with multiple criteria and list

countif unique values using multiple criteria

countif unique values using multiple criteria

Failed to listen on localhost:8000 (reason: Cannot assign requested address)

Loopback Error: connect ECONNREFUSED 127.0.0.1:3306 (MAMP)

How to import an asset in swift using Bundle.main.path() in a react-native native module

pump.io port in URL

Compiler error CS0246 (type or namespace not found) on using Ninject in ASP.NET vNext

BigQuery - concatenate ignoring NULL

ngClass error (Can't bind ngClass since it isn't a known property of div) in Angular 11.0.3

ggplotly no applicable method for 'plotly_build' applied to an object of class "NULL" if statements

Spring Boot JPA PostgreSQL Web App - Internal Authentication Error

How to remove the extra space from right in a webview?

java.lang.NullPointerException: Cannot read the array length because "<local3>" is null

Jquery different data trapped from direct mousedown event and simulation via $(this).trigger('mousedown');

flutter: dropdown item programmatically unselect problem

How to use merge windows unallocated space into Ubuntu using GParted?

Change dd-mm-yyyy date format of dataframe date column to yyyy-mm-dd

Nuget add packages gives access denied errors

Svchost high CPU from Microsoft.BingWeather app errors

Can't pre-populate phone number and message body in SMS link on iPhones when SMS app is not running in the background

12.04.3--- Dconf Editor won't show com>canonical>unity option

Any way to remove trailing whitespace *FOR EDITED* lines in Eclipse [for Java]?

maven-jaxb2-plugin cannot generate classes due to two declarations cause a collision in ObjectFactory class

Any way to remove trailing whitespace FOR EDITED lines in Eclipse [for Java]?