Count formula

[kazdima]

Hello,

i need help in counting the cells between 2 dates, and condition is- to count cells in Column "G" if the same name listed more than once in the given period. This formula counts duplicate names.
=IF(AND(H2>=EDATE(A2,-1),COUNTIF($G$2:G2,G2),H2<=EOMONTH(EDATE(A2,-1),0)),1,0)

Thank you.

[ChemistB]

Can you post an example workbook? (Go Advanced>Manage Attachments)

[kazdima]

Please see my file. Sheet "O_D" has highlighted cells with duplicate counts.

[ChemistB]

Okay, so Michelle in row 2 reappears again in rows 12 and 13. So in row 2 (what column?) you want to return 10 (12-2) or are we counting inclusive so 11? Then on row 12, we'd return a 1 or 2? Am I on the right track?

[kazdima]

If the same name, tha same dates range "1" should be only once, another cell empty.
So, cells P4, P20; O9, O13, O17 should be empty.

[ChemistB]

Okay, some comments, In O2 copied down, you have

=IF(AND(H2>=EDATE(A2,-1),COUNTIF($G$2:G2,G2),H2<=EOMONTH(EDATE(A2,-1),0)),1,0)

The middle term (COUNTIF($G$2:G2,G2)) will always be true. Do you mean to use COUNTIF($G$2:G2,G2)=1?

Anyway, to prevent a second visit within the same month as showing up as a 1, try this in O2 and P2

=IF(SUMPRODUCT(($G$2:$G2=G2)*($A$2:$A2=$A2))>1, 0, IF(AND(H2>=EDATE(A2,-1),COUNTIF($G$2:G2,G2),H2<=EOMONTH(EDATE(A2,-1),0)),1,0))

Again, removing that COUNTIF gives same results so get rid of that too.

=IF(SUMPRODUCT(($G$2:$G2=G2)*($A$2:$A2=$A2))>1, 0, IF(AND(H2>=EDATE(A2,-2),COUNTIF($G$2:G2,G2),H2<=EOMONTH(EDATE(A2,-2),0)),1,0))

Does that help?

[kazdima]

Hello ChemiseB,

Thank you for your formulas-) I am sending once more my file. Would you please look at it? especially at "Sum" file. "P2" formula does not pick up properly in row 8, R8.
Also, for some reason formulas do not work in rows 7,9,11,13,15... Possible- the reason in formatting in rows 4 and 5?

I would appreciate if you could fix the formulas-)

Thank you.

[ChemistB]

Okay, working one formula at a time. In E7,

=IFERROR((COUNTIFS(O_D!$A:$A,$E$5,O_D!$H:$H,">="&E5,O_D!$H:$H,"<="&E4))/E6,"")

There are no cells where A is january and H is also January. What are you looking for?

[ChemistB]

In E8, there is only a sum of 1 P2 in January. What are you expecting there and why?

[kazdima]

Yes, you are right regarding E7. Thank you.
E8 picks up numbers from sheet O_D Column "O". cells 12 and 16 have 1= 2. E8 shows 1.

[ChemistB]

Ahh, I believe the formula you want in row 8 is

=SUMIFS(tbData[[P2 ]],tbData[gfap],IF($I$2="All","*",$I$2),tbData[Location],$B$2,tbData[Unique patients],1,tbData[ymd_last_service],">="&E$5,tbData[ymd_last_service],"<"&EDATE(E$5,1))
You were looking at column "DATE" instead of "ymd_last_service"

[kazdima]

Sorry.. we do not have to pay attention to "ymd_last_service". We care about Date Columns A and H.

[ChemistB]

Okay, so with this formula, I believe you are not looking for "Date" which is column A but "ymd_first_service" which is column H.

=SUMIFS(tbData[[P2 ]],tbData[gfap],IF($I$2="All","*",$I$2),tbData[Location],$B$2,tbData[Unique patients],1,tbData[ymd_first_service],">="&E$5,tbData[ymd_first_service],"<"&EDATE(E$5,1))

[kazdima]

When I put in G8 this formula: .=SUMIFS(tbData[[P2 ]],tbData[gfap],IF($I$2="All","*",$I$2),tbData[Location],$B$2,tbData[Unique patients],1,tbData[ymd_first_service],">="&G$5,tbData[ymd_first_service],"<"&EDATE(G$5,1))
It give me "1" instead of "2".

[ChemistB]

Okay, we can go back to using "Date" (Col A) which will give you the correct value for Col G

Are you looking to SUM IF either A or H is within that month?

[kazdima]

It is better H. Please try test yourself first to match numbers in Sum from O_D. Thank you

[ChemistB]

Okay, switching back to Date In C8 copied (copy and paste) to D8:P8

=SUMIFS(tbData[[P2 ]],tbData[gfap],IF($I$2="All","*",$I$2),tbData[Location],$B$2,tbData[Unique patients],1,tbData[ymd_first_service],">="&G$5,tbData[ymd_first_service],"<"&EDATE(G$5,1))

This seems to be counting correctly.

[kazdima]

No...Unfortunately.. pls look at cell F8- it shows "1", but should be "2", if you look at column "O" in sheet O_D in cell O12 and O16 there are 1 and 1, so it should be 2 in F8..

[ChemistB]

For F8, must be a unique patient, only 1 of 2 "1's" in P2 (Col O) is a unique patient. Remove that criteria to get 2

[kazdima]

Ok. Thank you.

[ChemistB]

Glad to help