Posts by andrej

It works great. Thanks a lot.

Hello,

I have data exported but get them in only in one column. There are 2 types of data (email and No of emails sent) and follow each other:
[email protected]
77
[email protected]
88
name3[email protected]
968
....

I would like to convert this convert into two columns, so that I can make table and later all kind of reports from this data. Any suggestion how I can easy transform this column in 2?

Re: define range (C1:C24) with formula

It is little more clear now, but I would need to check indirect formula explanation again. Thanks though.

So you suggest it can be substituted for concatenate function? Just putting text together something like this? (of course the one below is not working - why would it on the dry run :P)
=countifs(CONCATENATE(';A1336;'!;$C;1;":";$C77");">1")

Re: define range (C1:C24) with formula

It works. Thanks.

Can I ask why I need to put cell number in brackets and & symbol on both sides? I mean, why doesn't it work without? So that I can understand it better for next use

Re: define range (C1:C24) with formula

Here is sample of my workbook.
forum.ozgrid.com/index.php?attachment/71969/

Re: define range (C1:C24) with formula

The first part is working, but the second one (green) is not working. It doesn't look like it is linking to cell.
=COUNTIFS(INDIRECT("'"&A1336&"'!$C$1:$C"&MATCH("meja";'A1336'!$C$1:$C10000;0));">1")

I also tried it like in the first part (not sure what the logic behind "&A1336&" is or why it is working in this way), but it also didn't work.
=COUNTIFS(INDIRECT("'"&A1336&"'!$C$1:$C"&MATCH("meja";'"&A1336&"'!$C$1:$C10000;0));">1")

Re: define range (C1:C24) with formula

Hi,
I got swamped with other work and was not able to check it sooner. Even though your were quick with your help. Sorry for that.

I checked it now, and this is working on the same sheet:
COUNTIFS(INDIRECT("C1:C"&MATCH("meja";C1:C10000;0));">1")

I also tried it with data from other sheet and it works.
COUNTIFS(INDIRECT("'Sheet1'!$C$1:$C"&MATCH("meja";'Sheet1'!$C$1:$C10000;0));">1")

Now I wanted to link the name of sheet to value in cell, since I have many repeats of this formula. But this I could not get to work. e.g.:
COUNTIFS(INDIRECT("'A1336'!$C$1:$C"&MATCH("meja";'A1336'!$C$1:$C10000;0));">1")

Maybe any ideas on how this could be solved?

Re: define range (C1:C24) with formula

Looks like interesting formula, but I am having problems putting it into context. I am not sure that I understand how it is used:

e.g. I tried to use this as part of formula - to define range, but even this is not working
INDIRECT("C1:C"&MATCH("meja";C:C;0);FALSE)

i was hoping to get range C1:C21 (number 21 in this case), and than I was thinking of putting this in formula in my first post. But clearly there are mistakes. Any suggestions on what I missed?

Hi,

I want to do countifs formula and for range of counting I would like to define with formula. e.g. instead of C1:C24 I would like to make formula that would return 24 (or different number depending on position). I tried it with match formula, but it doesnt want to work the way I entered it. Any idea what is wrong?

COUNTIFS('GENO-MED (genofit)'!$C$1:$C(match("meja";'Genome Solutions Sdn Bhd'!C:C;0));">1";'GENO-MED (genofit)'!$D$1:$D$(match("meja";'Genome Solutions Sdn Bhd'!D:D;0));"<>")

thanks

Hi,

I would like to enter data from sheet1 to appropriate fields in sheet2 (as shown in example). I thought I could do this with match and index formula, but am having problems, due to many criteria and big search field in sheet 1. Any ideas for help?
forum.ozgrid.com/index.php?attachment/71774/

Thanks,

Re: converting date format

I tried it again, and now it is working. No idea what was wrong last time.
Thanks for your help and effort

Re: Finding specific columns and counting in them

I am attaching sample file of how data are structured (sheet2) and how I will add week periods and count data for that period (sheet1).
forum.ozgrid.com/index.php?attachment/71068/

Re: converting date format

What do you mean by "formated as short date"?
In column E (from row 1 to row 1128) I have many different dates. I want to count number of cells where dates are between two specific dates. e.g. in column E are dates (12.12.2016; 14.12.2016; 20.12.2016; 21.12.2016) and I need to count number of dates between 12.12.2016 and 20.12.2016.

Since I will have to count for many different periods, that is why I would like to use reference to ranges - hence B1 and B2 (this was mistake in example in first post - it should be B2 in second part of formula (I have corrected it now).
But the formula I have requires date to be in special format ( =date(yyyy;mm;dd) and I have it in different (dd.mm.yyyy) and the formula is not working when I try to enter link to cell with date (B1 and B2).

Hi,

I have date written in form of 25.12.2016 in excel cells. I would like to reference to this date in formula, but the formula DATE requires that date is written as =DATE(2016;12;25). So I cannot just link to that cell e.g. =DATE (B1), and would need to somehow convert date format.
Is this possible to do it automatically or over some formula?

I want to count in here
=countifs(E1:E1128;">="&B1;E1:E1128;"<="&B2)

Thanks

Hi,

I would need to count number of specific entries between two columns.
I have columns with different dates in first row:
[TABLE="width: 653"]

[tr]

[TD="class: xl71, width: 85, align: right"]06.01.2017[/TD]
[TD="class: xl71, width: 85, align: right"]04.01.2017[/TD]
[TD="class: xl71, width: 95, align: right"]03.01.2017[/TD]
[TD="class: xl71, width: 88, align: right"]29.12.2016[/TD]
[TD="class: xl71, width: 109, align: right"]28.12.2016[/TD]
[TD="class: xl71, width: 103, align: right"]22.12.2016[/TD]
[TD="class: xl71, width: 88, align: right"]20.12.2016[/TD]

[/tr]

[/TABLE]

there are different values in the columns (e.g. sms or mail)

Then I have defined weeks, e.g. 26.12.2016 to 30.12.2016

I would like to count number of word "sms" in the columns where dates fall between above range. In this case this are only two columns (28.12.2016 and 29.12.2016).
If possible I would also like to define rows, so that it counts only from 1 to 1128 row.

Is there any way to do this?
Thank you

Re: Average if for specific year and month)

It is working. Thanks a lot.

Re: Average if for specific year and month)

Thanks a lot, it is working great.

Do you think it is possible to do something similar with standard deviation? I couldnt see stdevif option, so am not sure if it is possible

Hi,

I have columns like this example:

[TABLE="width: 237"]

[tr]

[td]

A

[/td]

[td]

B

[/td]

[td]

C

[/td]

[/tr]

[tr]

[td]

01.07.2016

[/td]

[td]

7

[/td]

[td][/td]

[/tr]

[tr]

[td]

01.06.2016

[/td]

[td]

2

[/td]

[td]

da

[/td]

[/tr]

[tr]

[td]

01.06.2016

[/td]

[td]

3

[/td]

[td][/td]

[/tr]

[tr]

[td]

01.06.2015

[/td]

[td]

4

[/td]

[td][/td]

[/tr]

[tr]

[td]

01.08.2015

[/td]

[td]

5

[/td]

[td]

da

[/td]

[/tr]

[tr]

[td]

01.05.2015

[/td]

[td]

0

[/td]

[td][/td]

[/tr]

[/TABLE]

I would like to calculate average for B, if C is blank, if B is more than 0, and for all months in year 2016. I got formula for first two parts (blank C and more than 0 in B), but months and dates are giving me problem. Any ideas?

AVERAGEIFS(B2:B7;C2:C7;"";B2:B7;"<>0";A2:A7;YEAR(2016)) - year is not working and couldnt even try for month as well

Thanks

Re: Mark if value between one of data ranges

Good formula. I could use it to automatically look for numbers. I guess I did it in two steps:

on the bottom of Sheet I have formula to look for the range number for partner
=VLOOKUP("partner name";data!$A$5:$C$68;2;FALSE)

and then I have conditional formatting for open numbers
=(COUNTIFS(A2;">="&$B$15;A2;"<="&$C$15)<>1)*NOT(ISBLANK(A2))

maybe this one is better, since it will happen some times that one partner uses his range and I have to add additional range which I can than add to countifs formula.

thanks again for all the help.

Re: Mark if value between one of data ranges

It worked. Thanks.

Additionally I would like to make another check. For each partner (as for "name" in table above) I have than new sheet where I have their codes written in rows. After i send them new package I enter it in new row under new code. The codes should always in range set in first tabele and I would like code to be marked if I accidentally set it outside of given range.

e.g. as in table above partner a has range 2000 to 2999, so on his Sheet2 (Sheet1 has table ranges as in first post) it would be table like:

[TABLE="width: 200"]

[tr]

[td]

partner a

[/td]

[/tr]

[tr]

[td]

2000

[/td]

[/tr]

[tr]

[td]

2001

[/td]

[/tr]

[tr]

[td]

2002

[/td]

[/tr]

[tr]

[td]

2003

[/td]

[/tr]

[tr]

[td]

2004

[/td]

[/tr]

[tr]

[td]

2005

[/td]

[/tr]

[tr]

[td]

3002

[/td]

[/tr]

[/TABLE]

Number in last row is outside given range and should be marked. I tried using your formula, but since it didnt work I guess I dont understand it completely:

=COUNTIFS($A$2:$A$7;">="&Sheet1!$B$61;$A$2:$A$7;"<="&Sheet1!$C$61)>1

partner a's range is in Sheet1 in row 61, where starting number is in column B and ending number of range is in column C.