Index and match using wildcard

Hi All

I have 2 sets of data, one represent online subscription portal and the other payment API. I want to summarise only the business( with reference ending with "b") subscriptions on daily basis and match its payment transmission using the payment API DATA. On the payment API , business subscriptions payments are transmitted in bulk, that is, one or two large payment of all transactions in a day so its a challenge matching them with the data from the online subscription portal. I've managed to calculate the total transactions and amount but unable to match it with the reference and also to show when two bulk payments are made instead of one. My formula seem to sum all into one even when 2 separated bulk payments are made. grateful if someone has an idea on how to calculate this.

Many thanks

Many thanks Lemice. I'm not actually looking at pulling all business subscriptions, My objective, as labelled ideal presentation, isto create a daily summary table using the online subscription portal data and matching this with what was processed under the Payment API data.

Flame, apologies, you're right . I did check again.

The amounts highlighted yellow in col C are meant to be the aggregate of all the transactions highlighted yellow in col H. These are processed separately so my intention is to have a formula to reconcile them on a daily basis to verify the bulk daily amounts being processed as payments for the daily transactions are accurate.

I calculated the 26 and 5 manually just to show just to how I want it broken down. However in my summary above of that I used an array formula with a wildcard lower case "b" to count and sum the business subscriptions.

Flame let's have a look at your formula perhaps that's what I'm looking for.

Thanks

Brilliant!! Awesome!! Many thanks Flame. Much appreciated