Countif Specific issue

[ZFrost27]

Hello. I'm trying to figure out a formula to pull very specific info from a large raw data spread sheet

I need to calculate the number of Account ID (colum a) that appear in the list 1 time but also have the charge date (colum G) of 11/1/2017. Oh and have a $ amount above $100 in Colum J.

I have tried several ways its simply beyond my ability. Any help would be great.

Data Range

	A	B	C	D	E	F	G	H	I	J	K
1	Account ID	Group	Period	Freq	Category	Disp Code	Charge Date	Due Date	Amount Paid	Amount Unpaid	Total Amount
2	258101000001	258	Nov-17	A	MF	A	9/30/2017	11/1/2017	$0.00	$718.78	$718.78
3	258101000004	258	Nov-17	A	MF	A	9/30/2017	11/1/2017	$136.00	$38.78	$174.78
4	258101000034	258	Nov-17	A	MF	A	9/30/2017	11/1/2017	$60.17	$658.61	$718.78
5	258101000035	258	Nov-17	A	MF	A	9/30/2017	11/1/2017	$130.17	$588.61	$718.78
6	258101000058	258	Nov-17	A	MF	A	9/30/2017	11/1/2017	$60.17	$658.61	$718.78
7	258101000062	258	Nov-17	A	MF	A	9/30/2017	11/1/2017	$60.17	$658.61	$718.78
8	258101000065	258	Nov-17	A	MF	A	9/30/2017	11/1/2017	$0.00	$638.95	$638.95
9	258101000065	258	Nov-16	A	MF	A	9/30/2017	11/1/2016	$0.00	$620.35	$620.35
10	258101000065	258	Nov-15	A	MF	A	9/30/2017	11/1/2015	$53.48	$537.32	$590.80
11	258101000087	258	Nov-17	A	MF	A	9/30/2017	11/1/2017	$60.17	$658.61	$718.78
12	258101000089	258	Nov-17	A	MF	A	9/30/2017	11/1/2017	$95.17	$623.61	$718.78
13	258101000093	258	Nov-17	A	MF	A	9/30/2017	11/1/2017	$60.17	$658.61	$718.78
14	258101000108	258	Nov-17	A	MF	A	9/30/2017	11/1/2017	$60.17	$658.61	$718.78
15	258101000109	258	Nov-17	A	MF	A	9/30/2017	11/1/2017	$165.17	$553.61	$718.78
16	258101000116	258	Nov-17	A	MF	A	9/30/2017	11/1/2017	$0.00	$862.52	$862.52
17	258101000116	258	Nov-16	A	MF	A	9/30/2017	11/1/2016	$0.00	$837.40	$837.40
18	258101000116	258	Nov-15	A	MF	A	9/30/2017	11/1/2015	$37.20	$760.32	$797.52
19	258101000116	258	Nov-05	A	MF	A	9/30/2017	11/1/2005	$70.00	$0.00	$70.00
20	258101000118	258	Nov-17	A	MF	A	9/30/2017	11/1/2017	$53.48	$665.30	$718.78

[bebo021999]

Could you attach a sample workbook?

Not every one like to manual input the sample data

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[ZFrost27]

Ok I attached a sample workbook to this post.

[sandy666]

is that what you want?

[ZFrost27]

Thank you for the quick responce.

Instead of the specific accounts i'm more looking for a count of the times that criteria is met.

How did you do that I didn't see any formulas?

[Root_]

Is this what you need?

=SUMPRODUCT(--(COUNTIF(A2:A200,A2:A200)=1),--(G2:G200=--"11/1/2017"),--(J2:J200>=100))

[sandy666]

Power Query (Get&Transform) = no formula, no vba

your criteria are incorrect about charge date.

charge date (colum G) of 11/1/2017

with this - count is 0

I changed file so re-read again

edit:
I used only 2 criteria: ID & Unpaid

in this example with all 3 criteria I need nothing to see count is 0

[shukla.ankur281190]

or try

Please Login or Register  to view this content.

[ZFrost27]

I will try them all and see what happenes. Thank you all so much

[ZFrost27]

or try

Please Login or Register  to view this content.

Would this also work with the idea of counting how many in colum A only appear once? Im sorry if this is confusing I hate asking for help and was trying to not make it to difficult to answer.

I will give the longer explanation maybe it would help.

I have a Raw data file of accounts with Tax years that they owe for so the account number may show up more then once if they owe more then 1 year. I am trying to design a way to pull into a report how many account owe for what charge years. So for example If I am asked for the number of accounts that only owe for 11/1/2017 and 11/1/2016 or just owe for 11/1/2017.. ect.

All of your answers are wonderful but I think my longer explanation may help get the answer if its even possible.

[sandy666]

Best way is to show what you want to achieve with your example and criteria (post#1)

I need to calculate the number of Account ID (colum a) that appear in the list 1 time but also have the charge date (colum G) of 11/1/2017. Oh and have a $ amount above $100 in Colum J.

[ZFrost27]

Ok I added a better example worksheet.

Basically what I need is:

How many accounts only owe for due date 11/1/2017
How many accounts only owe for due dates 11/1/2017 and 11/1/2016
How many accounts only owe for due dates 11/1/2017 and 11/1/2016 and 11/1/2015
How many accounts only owe for due dates 11/1/2017 and 11/1/2016 and 11/1/2015 and 11/1/2014

the data is a list of accounts that owe charge years of property taxes. also with the Frequency of Annual Even or Odd.

[sandy666]

Basically what I need is:

How many accounts only owe for due date 11/1/2017
How many accounts only owe for due dates 11/1/2017 and 11/1/2016
How many accounts only owe for due dates 11/1/2017 and 11/1/2016 and 11/1/2015
How many accounts only owe for due dates 11/1/2017 and 11/1/2016 and 11/1/2015 and 11/1/2014

Hope this is what you want
(PowerQuery or PivotTable)

[ZFrost27]

Thats very close except that I need a count of accounts that only owe 11/1/2017 ect. so like how many accounts only owe one year, how many owe 2 years and so on.


I think that is the only thing missing.

Colum A: How many accounts only owe the 11/1/2017
Colum B: How many accounts owe the 11/1/2017 and 11/1/2016

I'm sorry if thats how it is setup and i'm just not seeing it.

[sandy666]

I asked you to show example of the result(s) not new version of data

[ZFrost27]

Sorry about that. I'll show it in this post and attaching an example of the results i'm looking for.

Only Owes Freq. Total
11/1/2017 A
11/1/2017 E
11/1/2016 - 11/1/2017 A
11/1/2016 O
11/1/2015 - 11/1/2017 E
11/1/2015 - 11/1/2016 - 11/1/2017 A
11/1/2014 - 11/1/2015 - 11/1/2016 - 11/1/2017 A
11/1/2014 - 11/1/2016 O

[sandy666]

Your source data doesn't contain any E, O
besides why
01/11/2017 = A (US style: 11/01/2017)
and
01/11/2017 = E, magic?

[ZFrost27]

The A, E and O pertain to the frequency they pay.

A = Annual
E= Even year
O = Odd year.

11/1/2017 would be November 1st 2017

Basically people are billed there maintenance Fee/ taxes the first of November the prior year however some people are only billed odd years and others even year. Most are Annual.

The sample doesn't have the E and O but there are a small amount in the whole data file (50,000 lines)

[sandy666]

Sorry mate,
Example should be representative not just cutted

I'm tired of this thread, maybe someone else will understand your logic and give you solution

have a nice day

[ZFrost27]

Ok well thanks for trying.

I attached the sample file with accounts that have A E and o for whom ever can possibly help me out.

[JeteMc]

This proposed solution is based on Sandy's suggestion of using a pivot table.
The row labels are populated by the account ID's
The column labels are populated by the due dates sorted in order of newest to oldest and filtered to only include 11/1/17 - 11/1/14
The values are populated by the amount unpaid
At the top of the pivot table the number of accounts that owe more than $100 is displayed in cell B2 using the countifs function: =COUNTIFS(B5:B93,">"&100)
As the function is dragged across to each cell in row 2 the original 'B5:B93,">"&100' needs to be added manually as new criteria_range1 and criteria1 each time so that in E2 the formula reads: =COUNTIFS(B5:B93,">"&100,C5:C93,">"&100,D5:D93,">"&100,E5:E93,">"&100)
Let us know if you have any questions.