Friday, March 24, 2017

yogi_Combine An ARRAY Formula And A Vertical COUNTIFS Formula (per OP's spec)

Google Spreadsheet   Post  #2138
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Mar-24-2017
question by Nathan_Lee:
https://productforums.google.com/forum/?utm_medium=email&utm_source=ba_notification#!topic/docs/OIX0XP0lmms;context-place=forum/docs
How do I combine an ARRAY Formula and a vertical COUNTIFS formula?
I am trying to count the number of identical addresses/zip-codes that occur throughout their corresponding columns.  I can do this through a simple COUNTIFS formula, but as the spreadsheet grows, I need it to build out on its own.

=COUNTIFS(C:C,C2,D:D,D2)


I have attempted a corresponding Array Formula, but I don't know what I need to add/change in order to get it to count properly.  Any help would be appreciated!

=ARRAYFORMULA(IF(ROW(A:A)=1,"ARRAY - # of Events Here",IF(ISBLANK(B:B),"",COUNTIFS(C1:C,C1,D1:D,D1))))

The core of the formula is:
=ARRAYFORMULA(IF(ISBLANK(B:B),"",COUNTIFS(C1:C,C1,D1:D,D1)))


Below is a link to an example spreadsheet that shows what I am trying to accomplish:

https://docs.google.com/spreadsheets/d/1U8CsfFQ5yZwlieexaKime6dKMrkHutYP0j4HjEqvg54/edit?usp=sharing

Thanks for any help!