Multiple/complex criteria in Countifs

[Mr_Patrick]

Hi everyone,

I am working on a large dataset (+6000 entries) were each entry can be grouped pairwise, one recording some current data which will be later replaced by new data like so:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]2010
[/TD]
[TD]2011
[/TD]
[TD]2012
[/TD]
[TD]2013
[/TD]
[TD]2014
[/TD]
[TD]2015
[/TD]
[TD]2016
[/TD]
[/TR]
[TR]
[TD]Current
[/TD]
[TD]32
[/TD]
[TD]12
[/TD]
[TD][/TD]
[TD]54
[/TD]
[TD][/TD]
[TD]432
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]New
[/TD]
[TD]32
[/TD]
[TD]12
[/TD]
[TD][/TD]
[TD]54
[/TD]
[TD]1
[/TD]
[TD]432
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]Current
[/TD]
[TD]53
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]65
[/TD]
[TD]4
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]New
[/TD]
[TD][/TD]
[TD]4
[/TD]
[TD][/TD]
[TD][/TD]
[TD]65
[/TD]
[TD][/TD]
[TD]8
[/TD]
[/TR]
[TR]
[TD]etc
[/TD]
[TD]etc
[/TD]
[TD]etc
[/TD]
[TD]etc
[/TD]
[TD]etc
[/TD]
[TD]etc
[/TD]
[TD]etc
[/TD]
[TD]etc
[/TD]
[/TR]
</tbody>[/TABLE]

I wish to create a function that outputs as a percentage how many new values will be added if there is currently no new value. In the example above, assuming the data is only made up of these 4 rows, the function should output 50% (because out of the 6 blank cells in the 2 "Current" lines, 3 will be filled).
My intuition tells me to work with Countifs, but unfortunately since each criteria are assembled under an AND clause, thus working with something like :

[COUNTIFS(Data!$G$2:$G$6697;"=Current";Data!K$2:K$6697;"=";Data!$G$2:$G$6697;"=New";Data!K$2:K$6697;"<>")

does not work.
I also tried to make countifs only count only even cells (all the "Current" rows are on even cells ), but this is no good. (I try to avoid building macros as I have never done this).

Anyway, does someone can suggest me something?

Thank you in advance.

 

[Eric W]

Maybe:

=SUMPRODUCT((B2:H5="")*(B3:H6<>"")*(MOD(ROW(B2:H5),2)=0))/SUMPRODUCT((B2:H5="")*(MOD(ROW(B2:H5),2)=0))

or

=SUMPRODUCT((B2:H5="")*(B3:H6<>"")*(A2:A5="Current"))/SUMPRODUCT((B2:H5="")*(A2:A5="Current"))

 

[Mr_Patrick]

Maybe:

=SUMPRODUCT((B2:H5="")*(B3:H6<>"")*(MOD(ROW(B2:H5),2)=0))/SUMPRODUCT((B2:H5="")*(MOD(ROW(B2:H5),2)=0))

or

=SUMPRODUCT((B2:H5="")*(B3:H6<>"")*(A2:A5="Current"))/SUMPRODUCT((B2:H5="")*(A2:A5="Current"))

Oh wow, this works. (not 100% sure why though).

Thanks Eric. I really appreciate it.

 

[Eric W]

Go to this link

https://msdn.microsoft.com/en-us/library/office/mt709003.aspx

about halfway down, and it explains a bit how SUMPRODUCT works. It really could use some more examples though.

Glad I could help!