• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formula help

D

Dan_K_2014

Member
Hello,

I would appreciate anyone's help to make the formula working:

.=IFERROR(SUMPRODUCT(--(O_D!$B$2:$B$100000=$B$2),--(YEAR(O_D!$A$2:$A$100000)=YEAR(E$5)*(MONTH(O_D!$A$2:$A$100000)=MONTH(E$5),--(O_D!$M$2:$M$100000=1),O_D!$N$2:$N$100000),"")

Or possibly, another formula?

Thank you.
 
Missing a parenthesis after the YEAR(E$5) bit, to keep the year from being multiplied by the month in next portion. Corrected formula, reduced to simplify the date calculation:

=IFERROR(SUMPRODUCT(--(O_D!$B$2:$B$100000=$B$2),--(TEXT(O_D!$A$2:$A$100000,"yyyymm")=TEXT(E$5,"yyyymm")),--(O_D!$M$2:$M$100000=1),O_D!$N$2:$N$100000),"")
 
Luke,
how could I add one more condition here?
=IFERROR(SUMPRODUCT(--(O_D!$B$2:$B$100000=$B$2),--(TEXT(O_D!$A$2:$A$100000,"yyyymm")=TEXT(E$5,"yyyymm")),--(O_D!$M$2:$M$100000=1),O_D!$N$2:$N$100000),"")

if(I2=O_D!$K2$:$k$100000)
 
We'll just stick it in with another comma

=IFERROR(SUMPRODUCT(--(I2=O_D!$K2$:$k$100000),--(O_D!$B$2:$B$100000=$B$2),--(TEXT(O_D!$A$2:$A$100000,"yyyymm")=TEXT(E$5,"yyyymm")),--(O_D!$M$2:$M$100000=1),O_D!$N$2:$N$100000),"")
 
Thank you, Luke.
I have one more question to you.
I have a Data validation in Cell I2 for selection of 7 types of services which relate to column "K" in data file "O_D".

My question is: How can I count Total numbers in Column "N" if I am do not want to select of the services in I2, but want all in Total?

Thank you.
 
I would change Lukes last formula from:
=IFERROR(SUMPRODUCT(--(I2=O_D!$K2$:$k$100000),--(O_D!$B$2:$B$100000=$B$2),--(TEXT(O_D!$A$2:$A$100000,"yyyymm")=TEXT(E$5,"yyyymm")),--(O_D!$M$2:$M$100000=1),O_D!$N$2:$N$100000),"")

To: =IFERROR(SUMPRODUCT((I2=O_D!$K2$:$k$100000)*(O_D!$B$2:$B$100000=$B$2)*(TEXT(O_D!$A$2:$A$100000,"yyyymm")=TEXT(E$5,"yyyymm"))*(O_D!$M$2:$M$100000=1)*(O_D!$N$2:$N$100000)),"")

I think it is easier to read and understand when shown like that

In regards to adding the choice of another field:
simply add another block like:
(if(or(I2="All",I2=""),1,I2=O_D!$N2$:$N$100000))

This allows "All" or a blank to select all values

resulting in:

=IFERROR(SUMPRODUCT((if(or(I2="All",I2=""),1,I2=O_D!$N2$:$N$100000)) * (I2=O_D!$K2$:$k$100000) * (O_D!$B$2:$B$100000=$B$2) * (TEXT(O_D!$A$2:$A$100000,"yyyymm")=TEXT(E$5,"yyyymm")) * (O_D!$M$2:$M$100000=1) * (O_D!$N$2:$N$100000)),"")

Do you realise that your now comparing columns N & K to I2
 
I tried to upload my file that you could understand better how the file look slike, but there is no option for Excel file
 
There is a maximum size limit of 1MB
Can you save it as a *.xlsb (Binary File Type)

or save it to a Dropbox style account and share the link here
 
Strange, as people post Excel files here all the time

I have reset your user permissions

Can you log out and close your browser, then restart the browser and log back in

Then try again

If that doesn't work can you try a different browser?
 
Formula appears to be working correctly. Note that based on the dates in your example, the answer is 0. All the dates in raw data are pre-2015, while your report has formulas under dates for 2015.
 
It is not working for selection of "All".
Also, pls look at cells C5 and D5. is it going to work with such formula? because I am going to put the formula also to P2- in row 8. P2 is counting like this: If it is January, it counts who got first service in December; and P3- in November.
 

Attachments

  • RAR_Sent_20151021.xlsb
    47.5 KB · Views: 1
Not working for "all" because you don't have any cells in col K of the other sheet with a value of "All".

Formula in C5 should be:
=EDATE(D5,-1)
Copy to D5. I'm not sure which cell of the dates is the input...assuming it's E5, then you can make formula in F5 be:
=EDATE(E5,1)
and copy to the right.

The attached is what I would suggest to fix all the above.
 

Attachments

  • RAR LM.xlsb
    47.9 KB · Views: 2
Thank you, Luke.

Now the formula picks up numbers for “All” after I formatted data to a Table and copied your formula in.

Now there 2 more issues.

This formula does not work for P2 and P3 in rows 8 and 10.

P2= 1 month prior to P1. If P1 is January, the formula should count December New Patients.

P3=2 month prior to P1. If P1 is January, the formula should count November New Patients
 
Wouldn't the P2 value then for January 2014 be identical to P1 value for December 2013?
 
Not always. Because many patients do not continue service from month to month. First, we are looking at column "A"= reporting period. And for this reporting period we identify all categories of patients:(matching Clolumn "A" and "H")
1. New patients. MMYYYY in A= H
2. P2: A=H-less one month
3. P3: A=H- Less 2 months
4. P4: All the rest patients in the reporting period.
 
All these categories, except of P4 are counted in the Table in Columns N,O, P. I need just to count them for each reporting period. Under condition, that my formulas in those columns are correct.
I tried to use your formula with Table Names to change NEW PATIENTS to P2, P3, but I am doing smth wrong and it is not working. It looks like I do not know how to use table names in formula which you sent to me in the spreadsheet
 
Hi Dan,

I'm afraid I'm not understanding the counting system, and how you want to group the people. :(

Could you perhaps post a small sample of data, and manually fill in a table showing the results you want?
 
Hello Luke,



Yes, it is complicated-J)

I put expected number according to the results of formulas in columns N, O and P on the data file "O_D". I put sample data only for Jan-April 2014.

In the Row 30 of O_D sheet and in Rows 8,10 and 12 of “Sum” sheet. Row 6 formula is working.
It looks like you need to give me permission to upload a file
Jan-14

Feb-14

Mar-14

Apr-14

Total

P1

4

4

2

2

12

P2

1

1

2

1

5

P3

1

2

1

4

P4

3

1

2

6

Total

6

10

6

5

27
 
You must log in or register to reply here.
Back
Top