Count values in column, multiple criteria

sandromerlino · Post by **sandromerlino** » Mon Jul 16, 2018 12:25 pm

Good mornig to all,
I am sorry but I am not English mother tongue so I might make some mistakes in writing or not be very clear.
I use the Italian version of Open Office 4.1.1
I am writing to you to help me to solve this difficult topic.
The file has 3 sheets in 2 langueges.
In the first one:
You switch the language in the cell Q5
from January to December I have in the coloum J the personnel Shifts. It can contain different thinks but I need to count if the language is Italian "chiuso" or if the language is English "close"
In the second one:
You switch the language in the cell Q5
In N104 and Q104 I have 2 targets
In the coloumn H and K I have the Units sold and the Net sales for every day from January to December
In the third one:
In F107 and I107 I have the cells where I would like the formula
In the coloumn D and E there is a small explanation and olso in the coloumn I and J. This is just for january but this is an annual report so the formula should contain the other months calculation.
More details in case is not clear.
In January I am given 1 target for Quantity (d.N104) and 1 for Sales (d.Q104)
Let's take the examples of Quantity but is exactly the same for Sales
My manager want to explain this concept. The 1 January you suppose one flat target per day(risultato2.d111 =risultato2.e111 you have the same value). If the 1 January you sell more Quantity rispect to the flat target per day the target of the 2 January should decrease but he doesn't want to relax the team attention so he still want the flat target in the formula. If the 1 January you sell less Quantity rispect to the flat target per day the target of the 2 January should increase because the store has to take back the gap to reach the target.
This is so on till the end of the month.
This process should be done for February, March, ...., December

For any question please contact me

I enter the file
Thank you
Sandro

Edit: Changed subject, was Enter the highest value
Make your post understandable by others
-- MrProgrammer, forum moderator

sandromerlino · Post by **sandromerlino** » Mon Jul 16, 2018 1:21 pm

Good mornig to all,

I cannot enter the file
Can someone explain how to do

Thank you

Sandro

Post by **RusselB** » Mon Jul 16, 2018 3:03 pm

You state that you can't attach the file.
There are different reasons for this to happen.
The most common is that the file is too big for the forum, which has a 128k maximum file size.
You would've gotten a message stating why the file couldn't be attached.
If the reason is that the file is too big, then upload it to a file sharing service, like MediaFire or Dropbox and supply us with the link to the uploaded file.
Without that file, your explanation is, to me, not clear and would require a lot of back and forth communication to clarify.

sandromerlino · Post by **sandromerlino** » Mon Jul 16, 2018 5:26 pm

Dear RusselB
Please would you be kind to tell me how i can find MediaFire or Dropbox and address me if there is a password or something else to do
I am sorry

Thank you
Sandro

jrkrideau · Post by **jrkrideau** » Mon Jul 16, 2018 6:23 pm

Dropbox]
Mediafire]

sandromerlino · Post by **sandromerlino** » Mon Jul 16, 2018 7:18 pm

Dear jrkrideau,
I tried to do what you asked me . Has been very hard. I am not very good at doing this kind of things
Pease check if i did it correctly but if not I ll try again.
The one below should be the link. I cross fingers
https://www.dropbox.com/home?select=sce ... 1+.Eng.ods
email:sandromerlino@gmail.com
password:dropbox

Thank you

Sandro

sandromerlino · Post by **sandromerlino** » Tue Jul 17, 2018 9:41 pm

Dear all,
Please let me know if I have entered the file I was not able to attach in this topic correcly in the Dropbox
It is the first time I have done it and it was very difficult for me so I would like a double check
I used this:
https://www.dropbox.com/home?select=sce ... 1+.Eng.ods
email:sandromerlino@gmail.com
password:dropbox

Thank you

Sandro

sandromerlino · Post by **sandromerlino** » Thu Jul 19, 2018 8:52 pm

Dear all,
Unfortunately I have not got any help since i wrote this post. I do not know why. I might not have been clear
I'll try to split the problem in order to have this time more luck. My boss is complaining because the problem is still at the start point
Please

I would like to find the formula for this problem:
I need to divide the number in R115 by the number of time the range J125:J155 is different from "chiuso" or "close"
In the example:
1000/30
I wrote a formula in V115 but it doesn't work.
Please give me the formula in english and i'll traslate myself

I attach the file

Thank you so much

sandro

Post by **MrProgrammer** » Thu Jul 19, 2018 9:13 pm

sandromerlino wrote:the number of time the range J125:J155 is different from "chiuso" or "close"

=SUMPRODUCT(J125:J155<>"chiuso";J125:J155<>"close")
In your example, this formula has the value 30. Since this is to be a divisor, when that formula returns zero, a division will produce #DIV/0!. You may want to test for that case and produce an appropriate result instead of displaying that error.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

sandromerlino · Post by **sandromerlino** » Thu Jul 19, 2018 11:46 pm

Dear, MrProgrammer,
Thank you very much. The formula is perfect
Please I need another formula to solve another part of the problem.
Immagining that i am in January, could you please let me know the formula to calculate the number of time in the range (today:J155) is different from "chiuso" or "close"
For example:
today is the 14/1 the result should be 18 if there is no "chiuso" or "close" otherwise less the number of time it finds it
Consider I cannot change the coloumn F that is set as data.
The file is still the previuos one
Thank you very much for your super prompt previous response

Sandro

Post by **MrProgrammer** » Fri Jul 20, 2018 7:23 pm

sandromerlino wrote:Consider I cannot change the coloumn F that is set as data.

sandromerlino wrote:scelta del valore-01 .Eng - 01.ods

Your attachment has #VALUE! errors in column F.

sandromerlino wrote:let me know the formula to calculate the number of time in the range (today:J155) is different from "chiuso" or "close"

I am not sure I understand the request, but try:
=SUMPRODUCT(J125:J155<>"chiuso";J125:J155<>"close";F125:F155>=TODAY())
Otherwise study Conditional Counting and Summation before posting again.

Cell G126 wrote:

=IF(G125="lun";"mar";IF(G125="mar";"mer";IF(G125="mer";"gio";IF(G125="gio";"ven";IF(G125="ven";"sab";
 IF(G125="sab";"dom";IF(G125="dom";"lun";IF(G125="mon";"tue";IF(G125="tue";"wed";IF(G125="wed";"thu";
 IF(G125="thu";"fri";IF(G125="fri";"sat";IF(G125="sat";"sun";IF(G125="sun";"mon"))))))))))))))

[Tutorial] VLOOKUP questions and answers, Q20/A20

sandromerlino wrote:Enter the highest value

I don't understand how these questions are related to the topic you've used. No one would think to look in topic "Enter the highest value" for advice about conditional counting, though perhaps the confusion is related to being a non-native English speaker. Did you know that we have an Italian forum?

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

sandromerlino · Post by **sandromerlino** » Sat Jul 21, 2018 8:33 pm

Dear MrProgrammer,
Your 2nd formula is perfect and I am so glad that you are helping me in a such way PRECISE anf FAST . My boss is complaining much less.
You know, I might not have been clear in my first post explaining what is in my head . Actually the problem is difficult to explain even in Italian
We are reaching the point bit by bit. I think is better and it takes you not much time I guess
Please,I would like to have from you the last formula that helps me for the whole problem.
This time i attach the second sheet that was in the dropbox. We are going to use the 3rd one for the final formula
Considering that we are in January as before
I would like in the cell H122 for the range H125-H155 the sum of the figures from the first day of the month(H125) to the day before today (TODAY()-1)
Example:
If we are on the 5th of January. I would like the sum of 1-2-3-4 of january so H125-H126-H127-H128

I did my best and i thnk this is the formula. I do not want you to thnk that i just wait for you doing nothing.
Please confirm me that is correct
=SUMPRODUCT(H125:H155;E125:E155=today()-1)

I attach the file

Thank you so much

Sandro

sandromerlino · Post by **sandromerlino** » Sun Jul 29, 2018 12:36 pm

Dear MrProgrammer,
I would like to thank you very much for all the partial formulas that you gave me. They are all perfect and I am one step away from the final formula.
If you do not want to help me to find the final formula I respect your decision and I hope you will for another problem

Thank you a lot

sandro

edit this topic's initial post · Post by **Villeroy** » Sun Jul 29, 2018 1:33 pm

10 year old document of mine trying to explain SUMPRODUCT: download/file.php?id=2939
Green columns A2:C51 are random dummy data. They change when you hit Ctrl+Shift+F9.
The subsequent columns calculate the sumproduct of C where A and B are equal to the first pair A2:B2 but without using the SUMPRODUCT function.
D tests if this row's value in A equals A2. This returns TRUE (1) or FALSE (0).
E tests if this row's value in A equals B2. This returns TRUE (1) or FALSE (0).
F calculates the product of D, E and the value in C. If any of the values in C,D,E is zero, the whole product is zero. If both D and E equal 1, then F equals C, otherwise 0.

The red cells indicate numbers >0 by means of conditional formatting.
In F52 you find the sum of all the products in a yellow cell. This is our sumproduct without using SUMPRODUCT.

The other yellow cells show the same results as F52 without referencing any helper columns.
I2 does a SUMPRODUCT the Calc way and subsequent I3, I4, ... does the same thing for each row (sum if A and B equal this row's A and B).
K22 is compatible with Excel.
K23 uses does the same trick as SUMPRODUCT with an array formula summing up products of arrays.

sandromerlino · Post by **sandromerlino** » Sun Jul 29, 2018 10:38 pm

Dear all,
I hope someone will help me to find the final solution
I attached in my drop box: https://www.dropbox.com/home?select=sce ... 1+.Eng.ods - email:sandromerlino@gmail.com - password:dropbox
A new file named:scelta del valore- Eng-03. I am sorry I did my best to change the italian formulas into english formulas but I did not succeded because when you do it and you save the file, once you close and open the file again the computer rechange all of them.I do not know if you'll find the italian or english formulas.
I'll try to explain again what I have thanks to Mr Programmer.
The sheet to consider is risultato1 where the formulas are.
The file has 3 sheets in 2 langueges.
In the first one (T):
You switch the language in the cell Q5
from January to December I have in the coloum J the personnel Shifts. It can contain different thinks but I need to count if the language is Italian "chiuso" or if the language is English "close"

In the second one(D):
You switch the language in the cell Q5
In N104 and Q104 I have 2 targets for the month of January and so on till arrive N1204 and Q1204
In the coloumn H and K I have the Units sold and the Net sales for every day from January to December

In the third one(risultato1):
In F107 and I107 I have the cells where I would like the formula
In the coloumn D111-D122 I have the result dued to:Target of units sold /number of working days
In the coloumn E111-E122 I have the result dued to:(Target of units sold - number of units sold till yesterday) /number of working days from today to the end of the month
In the coloumn F111-F122 I have the result dued to:higher walue between the 2 number up
In the coloumn G111-G122 I have my way to have the result for each months
I would like you to help me to make one singol formula for the whole year if it is possible instead of having this part (=if(text(today();"mm")="01")that check the month
One more think that my boss does not want is that if the month has no working days and the result is (#div/0) the formula should show 0

If you have any doubt please d not hesitate
I changed this words in the formulas:
DATA-DATE
RIF.RIGA-ROW
GIORNI-DAYS
SE-IF
INDICE-INDEX
GIORNO.SETTIMANA-WEEKDAY
TESTO-TEXT
"ggg"-"ddd"
E-AND
MATR.SOMMA.PRODOTTO-SUMPRODUCT

Thank you

Sandro

sandromerlino · Post by **sandromerlino** » Fri Aug 03, 2018 9:18 am

Dear all
Please, I ask your help to transform these 12 formulas in one. The poin I am not able to do is to allow the formulas to take the part related to the actual month. I would like to delate this part =if(text(today();"mm")="..." which is repeted 12 times. If you can reduce it more than this is welcome

January