Conditional Aggregation with Partial Match

Hi Everyone,

I have attached my spreadsheet and am hoping someone can find a way of doing the following:

Under tab "PostCodes" you see the below, I want total Bookings to basically go through all of tab "Bookings" and count up all bookings that are made in the Year entered in Cell B1 of "PostCodes" that have the post code begin with the text in "PostCode" column. And then I also with to SUM the Total order value of those in the "Total Order Value" cell.

Year: 2007

PostCode Region Total Bookings Total Order Value
AB Aberdeen
AL St Albans
B Birmingham
BA Bath
BB Blackburn
BD Bradford
BH Bournemouth
BL Bolton
BN Brighton

Anyone know how to do this as I am going in absolute circles on this.

Thanks,

Chris

Chris, for info., both SUMIF & COUNTIF accept wildcards, however, in this instance you have an issue in so far as the post codes themselves are not mutually exclusive
B* would for ex. include B, BA, BB, BD etc... which is not what you want

Given the above, what I would suggest you do is add a further column to your source sheet which extracts the initial element of the post code:

Bookings!R2
=LEFT(G2,MIN(FIND({1,2,3,4,5,6,7,8,9,0},G2&1234567890)-1))
copied down

or if we assume 1 or 2 alphas only

=LEFT(G2,2-ISNUMBER(0+MID(G2,2,1)))
copied down

Then use exact matches on this column

PostCodes!C4:
=COUNTIF(Bookings!$R:$R,$A4)
copied down

PostCodes!D4:
=SUMIF(Bookings!$R:$R,$A4,Bookings!$M:$M)
copied down

For the sake of efficiency (calculations) modify as follows:

Bookings!R2:
=LEFT(G2,2-ISNUMBER(0+MID(G2,2,1)))&"@"&$Q2
copied down

Then

PostCodes!C4:
=COUNTIF(Bookings!$R:$R,$A4&"@"&$B$1)
copied down

PostCodes!D4:
=SUMIF(Bookings!$R:$R,$A4&"@"&$B$1,Bookings!$M:$M)
copied down

the more common approach (pre XL2007) is to use SUMPRODUCTs, however, these functions are inefficient esp. when used in large volume or with large precedent ranges.

Using a concatenated key as we have done in Col R is a good way of maintaining performance whilst still allowing for multi criteria tests.

If you are using XL2007 or above you can use COUNTIFS and SUMIFS respectively but note that they are not backwards compatible.