COUNTING Text Cells with Duplicates Based on Multi-base criteria

**TMains** · 02-09-2022, 06:37 PM

Hello,

This seemingly simple formula creation has been driving me nuts. Please help if you can.

Referencing the attachment, I am trying to create a formula for cell G5 and G6 that counts up the absolute number of calendar days from column C.

The challenge for me has been that the count has to be broken down by the two different "Systems" named in column B (CLCN 1 and CLNC 2).

Also, there are duplicate dates for each of the two different systems listed in column B.

I have tried various combos using functions to include IF, COUNTIF, SUM, AND, MATCH, FREQUENCY, and FILTER, but to no avail.

Any suggestions? Thanks.

**wk9128** · 02-09-2022, 09:50 PM

Cell F5 array formula , drag down

Formula:

Please Login or Register  to view this content.

Cell G5 array formula , drag down

Formula:

Please Login or Register  to view this content.

**menem** · 02-09-2022, 11:00 PM

If you don't mind to use helpers cells.

D5
=--(COUNTIFS(B$4:B5,B5,C$4:C5,C5)=1)
copy down

G5
=SUMIFS($D$4:$D$23,$B$4:$B$23,F5)
copy down

Regards.

**TMains** · 02-10-2022, 11:54 AM

This works nicely. Thank you, very much.

Tom

**TMains** · 02-10-2022, 12:04 PM

Thanks for the formulas. They work well for the current data set in columns B and C.

However, if I add new data to the bottom of the columns B and C, how can the formula be adjusted to look for the new data automatically without having to manually enter the new array information? Thanks for the help.

Tom

**JeteMc** · 02-11-2022, 01:58 PM

Hello TMains and Welcome to Excel Forum.
Working with wk9128's formulas:
1. Convert the range B4:B23 into an Excel table
2. Modify the array formula for column G to read: =COUNT(0/N(MATCH(F5&$C$5:$C$24,$B$5:$B$24&$C$5:$C$24,)=ROW(Table1[Date])-4))
Note that the data in B24:C24 was added after the above changes and the formulas automatically adjusted.
Let us know if you have any questions.

**TMains** · 02-14-2022, 10:51 AM

Thanks for the additional help. Your help made it work!

**JeteMc** · 02-14-2022, 02:26 PM

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

**wk9128** · 02-14-2022, 09:58 PM

@TMains You're Welcome. Glad to help . Thank You for the feedback and rep.

COUNTING Text Cells with Duplicates Based on Multi-base criteria

LinkBack

Thread Tools

Rate This Thread

Display

COUNTING Text Cells with Duplicates Based on Multi-base criteria

Re: COUNTING Text Cells with Duplicates Based on Multi-base criteria

Re: COUNTING Text Cells with Duplicates Based on Multi-base criteria

Re: COUNTING Text Cells with Duplicates Based on Multi-base criteria

Re: COUNTING Text Cells with Duplicates Based on Multi-base criteria

Re: COUNTING Text Cells with Duplicates Based on Multi-base criteria

Re: COUNTING Text Cells with Duplicates Based on Multi-base criteria

Re: COUNTING Text Cells with Duplicates Based on Multi-base criteria

Re: COUNTING Text Cells with Duplicates Based on Multi-base criteria

Thread Information

Users Browsing this Thread

Similar Threads

Count text formula based on multi-criteria excluding duplicates

[SOLVED] Counting values based on a criteria with duplicates

[SOLVED] Function to ignore rows based on blanks cells and counting without duplicates

Sum/Count/Average based on multi criteria in table, skip duplicates when not needed.

Count an array's frequency based on another arrays criteria,only counting duplicates once

[SOLVED] Copy Cells to another Worksheet Based on Multi Criteria

[SOLVED] Random selecting 20 items base on criteria with no duplicates

Bookmarks

Bookmarks

Posting Permissions