Multiple Condition/criteria sum function for excel

Forrás: http://www.ozgrid.com/VBA/sum-multi-criteria.htm

Function SumByCriteria(Sum_Range As Range, Criteria1, Criteria1Range As Range, _
Criteria2, Criteria2Range As Range, Optional Criteria3, _
Optional Criteria3Range As Range, Optional Criteria4, _
Optional Criteria4Range As Range, Optional Criteria5, _
Optional Criteria5Range As Range) As Long

Dim lLoopStop As Long, lLoop As Long, rRange As Range, lRow As Long
Dim sTotal As Single, bVal1 As Boolean, bVal2 As Boolean, bVal3 As Boolean
Dim bVal4 As Boolean, bVal5 As Boolean, bVal1b As Boolean, bVal2b As Boolean, bVal3b As Boolean
Dim bVal4b As Boolean, bVal5b As Boolean, lCriteriaUsed As Long

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''Written by ozgrid.com''''''''''''''''''''''''''''''''''''''
'Sums Values in Sum_Range when up to 5 conditions are met in corresponding cells.
'All ranges used should as small as possible for efficieny
''IF YOU HAVE 2007 USE SUMIFS
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''


lLoopStop = WorksheetFunction.CountIf(Criteria1Range, Criteria1)

bVal3 = Not Criteria3Range Is Nothing
bVal4 = Not Criteria4Range Is Nothing
bVal5 = Not Criteria5Range Is Nothing

If bVal5 = False Then lCriteriaUsed = 4
If bVal4 = False Then lCriteriaUsed = 3
If bVal3 = False Then lCriteriaUsed = 2

Set rRange = Criteria1Range(1, 1)
For lLoop = 1 To lLoopStop

Set rRange = Criteria1Range.Find(Criteria1, rRange, _
xlFormulas, xlWhole, xlByRows, xlNext, False)

lRow = rRange.Row

If bVal5 = True Then bVal5b = Criteria5Range(lRow, 1) = Criteria5
If bVal4 = True Then bVal4b = Criteria4Range(lRow, 1) = Criteria4
If bVal3 = True Then bVal3b = Criteria3Range(lRow, 1) = Criteria3
bVal2b = Criteria2Range(lRow, 1) = Criteria2
bVal1b = Criteria1Range(lRow, 1) = Criteria1

If lCriteriaUsed > 4 Then
If bVal5b And bVal4b And bVal3b And bVal2 And bVal1b Then
sTotal = WorksheetFunction.Sum(Sum_Range(lRow, 1), sTotal)
End If
ElseIf lCriteriaUsed > 3 Then
If bVal4b And bVal3b And bVal2b And bVal1b Then
sTotal = WorksheetFunction.Sum(Sum_Range(lRow, 1), sTotal)
End If
ElseIf lCriteriaUsed > 2 Then
If bVal3b And bVal2 And bVal1b Then
sTotal = WorksheetFunction.Sum(Sum_Range(lRow, 1), sTotal)
End If
ElseIf bVal2b And bVal1b Then
sTotal = WorksheetFunction.Sum(Sum_Range(lRow, 1), sTotal)
End If

Next lLoop
SumByCriteria = sTotal
End Function

Example usage;

=SumByCriteria(A1:A21,"cat",C1:C21,"furry",E1:E21,"fluffy",G1:G21,"persian",I1:I21)

Fontosabb Excel 2007 függvények magyar-angolul

Bővítmények és automatizálási függvények Add-in and Automation functions
KIMUTATÁSADATOT.VESZ GETPIVOTDATA A kimutatásokban tárolt adatok visszaadására használható.

Adatbázis-kezelő függvények Database functions
AB.ÁTLAG DAVERAGE A kijelölt adatbáziselemek átlagát számítja ki.
AB.DARAB DCOUNT Megszámolja, hogy az adatbázisban hány cella tartalmaz számokat.
AB.DARAB2 DCOUNTA Megszámolja az adatbázisban lévő nem üres cellákat.
AB.MEZŐ DGET Egy adatbázisból egyetlen olyan rekordot ad vissza, amely megfelel a megadott feltételeknek.
AB.MAX DMAX A kiválasztott adatbáziselemek közül a legnagyobb értéket adja eredményül.
AB.MIN DMIN A kijelölt adatbáziselemek közül a legkisebb értéket adja eredményül.
AB.SZORZAT DPRODUCT Az adatbázis megadott feltételeknek eleget tevő rekordjaira összeszorozza a megadott mezőben található számértékeket, és eredményül ezt a szorzatot adja.
AB.SZÓRÁS DSTDEV A kijelölt adatbáziselemek egy mintája alapján megbecsüli a szórást.
AB.SZÓRÁS2 DSTDEVP A kijelölt adatbáziselemek teljes sokasága alapján kiszámítja a szórást.
AB.SZUM DSUM Összeadja a feltételnek megfelelő adatbázisrekordok mezőoszlopában a számokat.

Dátumfüggvények Date and time functions
DÁTUM DATE Adott dátum dátumértékét adja eredményül.
DÁTUMÉRTÉK DATEVALUE Szövegként megadott dátumot dátumértékké alakít át.
NAP DAY Dátumértéket a hónap egy napjává (0-31) alakít.
NAP360 DAYS360 Két dátum közé eső napok számát számítja ki a 360 napos év alapján.
EDATE EDATE Adott dátumnál adott számú hónappal korábbi vagy későbbi dátum dátumértékét adja eredményül.
EOMONTH EOMONTH Adott dátumnál adott számú hónappal korábbi vagy későbbi hónap utolsó napjának dátumértékét adja eredményül.
ÓRA HOUR Időértéket órákká alakít.
PERC MINUTE Időértéket percekké alakít.
HÓNAP MONTH Időértéket hónapokká alakít.
NETWORKDAYS NETWORKDAYS Két dátum között a teljes munkanapok számát adja meg.
MOST NOW A napi dátum dátumértékét és a pontos idő időértékét adja eredményül.
MPERC SECOND Időértéket másodpercekké alakít át.
IDŐ TIME Adott időpont időértékét adja meg.
IDŐÉRTÉK TIMEVALUE Szövegként megadott időpontot időértékké alakít át.
MA TODAY A napi dátum dátumértékét adja eredményül.
HÉT.NAPJA WEEKDAY Dátumértéket a hét napjává alakítja át.
WEEKNUM WEEKNUM Visszatérési értéke egy szám, amely azt mutatja meg, hogy a megadott dátum az év hányadik hetére esik.
WORKDAY WORKDAY Adott dátumnál adott munkanappal korábbi vagy későbbi dátum dátumértékét adja eredményül.
ÉV YEAR Sorszámot évvé alakít át.
YEARFRAC YEARFRAC Az adott dátumok közötti teljes napok számát törtévként adja meg.

Információs függvények Information functions
CELLA CELL Egy cella formátumára, elhelyezkedésére vagy tartalmára vonatkozó adatokat ad eredményül.
HIBA.TÍPUS ERROR.TYPE Egy hibatípushoz tartozó számot ad eredményül.
INFÓ INFO A rendszer- és munkakörnyezet pillanatnyi állapotáról ad felvilágosítást.
ÜRES ISBLANK Eredménye IGAZ, ha az érték üres.
HIBA ISERR Eredménye IGAZ, ha az érték valamelyik hibaérték a #HIÁNYZIK kivételével.
HIBÁS ISERROR Eredménye IGAZ, ha az érték valamelyik hibaérték.
ISEVEN ISEVEN Eredménye IGAZ, ha argumentuma páros szám.
LOGIKAI ISLOGICAL Eredménye IGAZ, ha az érték logikai érték.
NINCS ISNA Eredménye IGAZ, ha az érték a #HIÁNYZIK hibaérték.
NEM.SZÖVEG ISNONTEXT Eredménye IGAZ, ha az érték nem szöveg.
SZÁM ISNUMBER Eredménye IGAZ, ha az érték szám.
ISODD ISODD Eredménye IGAZ, ha argumentuma páratlan szám.
HIVATKOZÁS ISREF Eredménye IGAZ, ha az érték hivatkozás.
SZÖVEG.E ISTEXT Eredménye IGAZ, ha az érték szöveg.
N N Argumentumának értékét számmá alakítja.
HIÁNYZIK NA Eredménye a #HIÁNYZIK hibaérték.
TÍPUS TYPE Érték adattípusának azonosítószámát adja eredményül.

Logikai függvények Logical functions
ÉS AND Eredménye IGAZ, ha minden argumentuma IGAZ.
HAMIS FALSE A HAMIS logikai értéket adja eredményül.
HA IF Logikai vizsgálatot hajt végre.
HAHIBA IFERROR A megadott értéket adja vissza, ha egy képlet hibához vezet; más esetben a képlet értékét adja eredményül.
NEM NOT Argumentuma értékének ellentettjét adja eredményül.
VAGY OR Eredménye IGAZ, ha bármely argumentuma IGAZ.
IGAZ TRUE Az IGAZ logikai értéket adja eredményül.

Keresési és hivatkozási függvények Lookup and reference functions
CÍM ADDRESS A munkalap egy cellájára való hivatkozást adja szövegként eredményül.
TERÜLET AREAS Hivatkozásban a területek számát adja eredményül.
VÁLASZT CHOOSE Értékek listájából választ ki egy elemet.
OSZLOP COLUMN Egy hivatkozás oszlopszámát adja eredményül.
OSZLOPOK COLUMNS A hivatkozásban található oszlopok számát adja eredményül.
VKERES HLOOKUP A megadott tömb felső sorában adott értékű elemet keres, és a megtalált elem oszlopából adott sorban elhelyezkedő értékkel tér vissza.
HIPERHIVATKOZÁS HYPERLINK Hálózati kiszolgálón, intraneten vagy az interneten tárolt dokumentumot megnyitó parancsikont vagy hivatkozást hoz létre.
INDEX INDEX Tömb- vagy hivatkozás indexszel megadott értékét adja vissza.
INDIREKT INDIRECT Szöveg megadott hivatkozást ad eredményül.
KERES LOOKUP Vektorban vagy tömbben keres meg értékeket.
HOL.VAN MATCH Hivatkozásban vagy tömbben értékeket keres.
OFSZET OFFSET Hivatkozás egy másik hivatkozástól számított távolságát adja meg.
SOR ROW Egy hivatkozás sorának számát adja meg.
SOROK ROWS Egy hivatkozás sorainak számát adja meg.
RTD RTD Valós idejű adatokat keres vissza a COM automatizmust (automatizálás: Egy alkalmazás objektumaival való munka másik alkalmazásból vagy fejlesztőeszközből. A korábban OLE automatizmusnak nevezett automatizálás iparági szabvány, a Component Object Model (COM) szolgáltatása.) támogató programból.
TRANSZPONÁLÁS TRANSPOSE Egy tömb transzponáltját adja eredményül.
FKERES VLOOKUP A megadott tömb bal szélső oszlopában megkeres egy értéket, majd annak sora és a megadott oszlop metszéspontjában levő értéked adja eredményül.

Matematikai és trigonometrikus függvények Math and trigonometry functions
ABS ABS Egy szám abszolút értékét adja eredményül.
PADLÓ FLOOR Egy számot lefelé, a nulla felé kerekít.
INT INT Egy számot lefelé kerekít a legközelebbi egészre.
MARADÉK MOD Egy szám osztási maradékát adja eredményül.
HATVÁNY POWER Egy szám adott kitevőjű hatványát számítja ki.
SZORZAT PRODUCT Argumentumai szorzatát számítja ki.
VÉL RAND Egy 0 és 1 közötti véletlen számot ad eredményül.
KEREKÍTÉS ROUND Egy számot adott számú számjegyre kerekít.
KEREKÍTÉS.LE ROUNDDOWN Egy számot lefelé, a nulla felé kerekít.
KEREKÍTÉS.FEL ROUNDUP Egy számot felfelé, a nullától távolabbra kerekít.
GYÖK SQRT Egy szám pozitív négyzetgyökét számítja ki.
RÉSZÖSSZEG SUBTOTAL Lista vagy adatbázis részösszegét adja eredményül.
SZUM SUM Összeadja az argumentumlistájában lévő számokat.
SZUMHA SUMIF A megadott feltételeknek eleget tevő cellákban található értékeket adja össze.
SZORZATÖSSZEG SUMPRODUCT A megfelelő tömbelemek szorzatának összegét számítja ki.

Statisztikai függvények Statistical functions
ÁTL.ELTÉRÉS AVEDEV Az adatpontoknak átlaguktól való átlagos abszolút eltérését számítja ki.
ÁTLAG AVERAGE Argumentumai átlagát számítja ki.
ÁTLAGA AVERAGEA Argumentumai átlagát számítja ki (beleértve a számokat, szöveget és logikai értékeket).
ÁTLAGHA AVERAGEIF A megadott feltételnek eleget tévő tartomány celláinak átlagát (számtani közepét) adja eredményül.
DARAB COUNT Megszámolja, hogy argumentumlistájában hány szám található.
DARAB2 COUNTA Megszámolja, hogy argumentumlistájában hány érték található.
DARABÜRES COUNTBLANK Egy tartományban összeszámolja az üres cellákat.
DARABTELI COUNTIF Egy tartományban összeszámolja azokat a cellákat, amelyek eleget tesznek a megadott feltételnek.
MAX MAX Az argumentumai között szereplő legnagyobb számot adja meg.
MAX2 MAXA Az argumentumai között szereplő legnagyobb számot adja meg (beleértve a számokat, szöveget és logikai értékeket).
MEDIÁN MEDIAN Adott számhalmaz mediánját számítja ki.
MIN MIN Az argumentumai között szereplő legkisebb számot adja meg
MIN2 MINA Az argumentumai között szereplő legkisebb számot adja meg, beleértve a számokat, szöveget és logikai értékeket.
MÓDUSZ MODE Egy adathalmazból kiválasztja a leggyakrabban előforduló számot.
SZÓRÁS STDEV Egy statisztikai sokaság mintájából kiszámítja annak szórását.
SZÓRÁSA STDEVA Egy statisztikai sokaság mintájából kiszámítja annak szórását (beleértve a számokat, szöveget és logikai értékeket).
SZÓRÁSP STDEVP Egy statisztikai sokaság egészéből kiszámítja annak szórását.
SZÓRÁSPA STDEVPA Egy statisztikai sokaság egészéből kiszámítja annak szórását (beleértve számokat, szöveget és logikai értékeket).

Szövegműveletekhez használható függvények Text functions
KARAKTER CHAR A kódszámmal meghatározott karaktert adja eredményül.
KÓD CODE Karaktersorozat első karakterének numerikus kódját adja eredményül.
ÖSSZEFŰZ CONCATENATE Több szövegelemet egyetlen szöveges elemmé fűz össze.
AZONOS EXACT Megvizsgálja, hogy két érték azonos-e.
SZÖVEG.TALÁL, SZÖVEG.TALÁL2 FIND, FINDB Karaktersorozatot keres egy másikban (a kis- és nagybetűk megkülönböztetésével).
BAL, BAL2 LEFT, LEFTB Szöveg bal szélső karaktereit adja eredményül.
HOSSZ, HOSSZ2 LEN, LENB Szöveg karakterekben mért hosszát adja eredményül.
KISBETŰ LOWER Szöveget kisbetűssé alakít át.
KÖZÉP, KÖZÉP2 MID, MIDB A szöveg adott pozíciójától kezdve megadott számú karaktert ad vissza eredményként.
CSERE, CSERE2 REPLACE, REPLACEB A szövegen belül karaktereket cserél.
SOKSZOR REPT Megadott számú alkalommal megismétel egy szövegrészt.
JOBB, JOBB2 RIGHT, RIGHTB Szövegrész jobb szélső karaktereit adja eredményül.
SZÖVEG.KERES, SZÖVEG.KERES2 SEARCH, SEARCHB Karaktersorozatot keres egy másikban (a kis- és nagybetűk között nem tesz különbséget).
HELYETTE SUBSTITUTE Szövegben adott karaktereket másikra cserél.
T T Argumentumát szöveggé alakítja át.
SZÖVEG TEXT Számértéket alakít át adott számformátumú szöveggé.
TRIM TRIM A szövegből eltávolítja a szóközöket.
NAGYBETŰS UPPER Szöveget nagybetűssé alakít át.
ÉRTÉK VALUE Szöveget számmá alakít át.

Excel: hibás értékek megszámolása

To count the number of cells that contain an error value (this is an array formula, entered with Ctrl+Shift+Enter):
=SUM(IF(ISERR(data),1,0))
{=SUM(IF(ISERR(data),1,0))}

{=SZUM(HA(HIBA(A1:A17);1;0))}
{=SZUM(HA(HIBÁS(A1:A17);1;0))}

• SZUM SUM Összeadja az argumentumlistájában lévő számokat.
• HIBA ISERR Eredménye IGAZ, ha az érték valamelyik hibaérték a #HIÁNYZIK kivételével.
• HIBÁS ISERROR Eredménye IGAZ, ha az érték valamelyik hibaérték.

Converting Unix Timestamps

If you import data you might encounter time values stored as Unix timestamps. Unix time is defined as the number of seconds since midnight (GMT time) on January 1, 1970 -- also known as the Unix epoch.

For example, here's the Unix timestamp for August 4, 2008 at 10:19:08 pm (GMT):
1217888348

To create an Excel formula to convert a Unix timestamp to a readable data and time, start by converting the seconds to days. This formula assumes that the Unix timestamp is in cell A1:
=(((A1/60)/60)/24)

Then, you need to add the result to the date value for January 1, 1970. The modified formula is:
=(((A1/60)/60)/24)+DATE(1970,1,1)

Finally, you need to adjust the formula for the GMT offset. For example, if you're in New York the GMT offset is -5. Therefore, the final formula is:
=(((A1/60)/60)/24)+DATE(1970,1,1)+(-5/24)

A simpler (but much less clear) formula that returns the same result is:
=(A1/86400)+25569+(-5/24)

Both of these formulas return a date/time serial number, so you need to apply a number format to make it readable as a date and time.

Forrás: Spreadsheetpage.com

Excel: hang lejátszása adott feltétel teljesülésekor

The Alarm function

Copy the code below to a VBA module in your workbook.
'Windows API function declaration
Private Declare Function PlaySound Lib "winmm.dll" _
Alias "PlaySoundA" (ByVal lpszName As String, _
ByVal hModule As Long, ByVal dwFlags As Long) As Long

Function Alarm(Cell, Condition)
Dim WAVFile As String
Const SND_ASYNC = &H1
Const SND_FILENAME = &H20000
On Error GoTo ErrHandler
If Evaluate(Cell.Value & Condition) Then
WAVFile = ThisWorkbook.Path & "\sound.wav" 'Edit this statement
Call PlaySound(WAVFile, 0&, SND_ASYNC Or SND_FILENAME)
Alarm = True
Exit Function
End If
ErrHandler:
Alarm = False
End Function


Following are examples of formulas that use this function:
=Alarm(A1,">=1000")

Forrás: Spreadsheetpage.com

Utolsó nem üres cella sorszáma, értéke

The LASTINCOLUMN function

Function LASTINCOLUMN(rngInput As Range)
Dim WorkRange As Range
Dim i As Long, CellCount As Long
Application.Volatile
Set WorkRange = rngInput.Columns(1).EntireColumn
Set WorkRange = Intersect(WorkRange.Parent.UsedRange, WorkRange)
CellCount = WorkRange.Count
For i = CellCount To 1 Step -1
If Not IsEmpty(WorkRange(i)) Then
LASTINCOLUMN = WorkRange(i).Value
Exit Function
End If
Next i
End Function

Utolsó érték sorszáma esetén: LASTINCOLUMN = WorkRange(i).row

The LASTINROW function

Function LASTINROW(rngInput As Range) As Variant
Dim WorkRange As Range
Dim i As Long, CellCount As Long
Application.Volatile
Set WorkRange = rngInput.Rows(1).EntireRow
Set WorkRange = Intersect(WorkRange.Parent.UsedRange, WorkRange)
CellCount = WorkRange.Count
For i = CellCount To 1 Step -1
If Not IsEmpty(WorkRange(i)) Then
LASTINROW = WorkRange(i).Value
Exit Function
End If
Next i
End Function

Forrás: Spreadsheetpage.com

Excel Konvertálás UTF-8 -ra

Private Function UTF8_Encode(ByVal sStr As String)
' Kiegészítés Excel-hez, hogy tudjon UTF-8 kódlapra konvertálni
For l& = 1 To Len(sStr)
lChar& = AscW(Mid(sStr, l&, 1))
If lChar& < 128 Then
sUtf8$ = sUtf8$ + Mid(sStr, l&, 1)
ElseIf ((lChar& > 127) And (lChar& < 2048)) Then
sUtf8$ = sUtf8$ + Chr(((lChar& \ 64) Or 192))
sUtf8$ = sUtf8$ + Chr(((lChar& And 63) Or 128))
Else
sUtf8$ = sUtf8$ + Chr(((lChar& \ 144) Or 234))
sUtf8$ = sUtf8$ + Chr((((lChar& \ 64) And 63) Or 128))
sUtf8$ = sUtf8$ + Chr(((lChar& And 63) Or 128))
End If
Next l&

UTF8_Encode = sUtf8$
End Function

Forrás:
http://prog.hu/tudastar/91087/Excel+vba+utf8+export+bom+nelkul.html

Excelben Max érték keresése tömbképlettel

Egy vízszintes tartományból (melyben dátumok vannak) kell kikeresnem egy adott dátumnál kisebb vagy egyenlő dátumok közül a maximumot.

{=MAX(HA($A$1:$A$10<=adott_datum;$A$1:$A$10))}

Forrás: Index Excel fórum

Excel: Könyvtár létezése függvény

' Check if a directory exists

Public Function CheckDir(Directory As String) As Boolean
On Error GoTo ErrNotExist
ChDir (Directory)
CheckDir = True
Exit Function
ErrNotExist:
CheckDir = False
End Function

Forrás: http://www.asap-utilities.com/excel-tips-detail.php?categorie=9&m=97