Frequency

P

Pefird

New Member
Joined
Mar 15, 2019
Messages
5
There are several post on FREQUENCY here but I cannot seem to adapt them to my needs. I have several variables that FREQUENCY is tied to and I just cannot make them work. Any help would be greatly appreciated.


~~There are several formulas with frequency on here, but for the life of me I cannot get them to work on my sheets. Mine needs are a little more complex and I am unable to adapt them.
[TABLE="width: 604"]
<colgroup><col width="107" style="width: 80pt; mso-width-source: userset; mso-width-alt: 3913;"> <col width="99" style="width: 74pt; mso-width-source: userset; mso-width-alt: 3620;"> <col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;"> <col width="68" style="width: 51pt; mso-width-source: userset; mso-width-alt: 2486;"> <col width="78" style="width: 59pt; mso-width-source: userset; mso-width-alt: 2852;"> <col width="69" style="width: 52pt; mso-width-source: userset; mso-width-alt: 2523;"> <col width="71" style="width: 53pt; mso-width-source: userset; mso-width-alt: 2596;"> <col width="64" style="width: 48pt;"> <col width="110" style="width: 83pt; mso-width-source: userset; mso-width-alt: 4022;"> <col width="64" style="width: 48pt;"> <tbody>[TR]
[TD="width: 107, bgcolor: transparent"]Collection Method/ Trap Type[/TD]
[TD="width: 99, bgcolor: transparent"]Attractant(s) Used[/TD]
[TD="width: 75, bgcolor: transparent"]ID for Collection Location[/TD]
[TD="width: 68, bgcolor: transparent"]Trap Pickup Date[/TD]
[TD="width: 78, bgcolor: transparent"]Genus[/TD]
[TD="width: 69, bgcolor: transparent"]Species[/TD]
[TD="width: 71, bgcolor: transparent"]# Females Collected[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 110, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]UV Light Trap[/TD]
[TD="bgcolor: transparent"]CO2 With Light[/TD]
[TD="bgcolor: transparent"] S1[/TD]
[TD="bgcolor: transparent"]2/12/2019[/TD]
[TD="bgcolor: transparent"] Aedes[/TD]
[TD="bgcolor: transparent"]vexans[/TD]
[TD="bgcolor: transparent"]10[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]UV Light Trap[/TD]
[TD="bgcolor: transparent"]CO2 With Light[/TD]
[TD="bgcolor: transparent"] S6[/TD]
[TD="bgcolor: transparent"]2/12/2019[/TD]
[TD="bgcolor: transparent"] Aedes[/TD]
[TD="bgcolor: transparent"]sollicitans[/TD]
[TD="bgcolor: transparent"]50[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Begin date:[/TD]
[TD="bgcolor: transparent, align: right"]12-Feb[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]UV Light Trap[/TD]
[TD="bgcolor: transparent"]CO2 With Light[/TD]
[TD="bgcolor: transparent"] S1[/TD]
[TD="bgcolor: transparent"]2/12/2019[/TD]
[TD="bgcolor: transparent"] Psorophora[/TD]
[TD="bgcolor: transparent"]ferox[/TD]
[TD="bgcolor: transparent"]5[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]End date:[/TD]
[TD="bgcolor: transparent, align: right"]1-Mar[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]UV Light Trap[/TD]
[TD="bgcolor: transparent"]CO2 With Light[/TD]
[TD="bgcolor: transparent"] S1[/TD]
[TD="bgcolor: transparent"]2/13/2019[/TD]
[TD="bgcolor: transparent"] Aedes[/TD]
[TD="bgcolor: transparent"]vexans[/TD]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]UV Light Trap[/TD]
[TD="bgcolor: transparent"]CO2 With Light[/TD]
[TD="bgcolor: transparent"] S6[/TD]
[TD="bgcolor: transparent"]2/13/2019[/TD]
[TD="bgcolor: transparent"] Aedes[/TD]
[TD="bgcolor: transparent"]vexans[/TD]
[TD="bgcolor: transparent"]4[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Trap Types[/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]UV Light Trap[/TD]
[TD="bgcolor: transparent"]CO2 With Light[/TD]
[TD="bgcolor: transparent"] S1[/TD]
[TD="bgcolor: transparent"]2/26/2019 [/TD]
[TD="bgcolor: transparent"] Aedes[/TD]
[TD="bgcolor: transparent"]vexans[/TD]
[TD="bgcolor: transparent"]6[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]New Jersey Trap[/TD]
[TD="bgcolor: transparent"]

[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]UV Light Trap[/TD]
[TD="bgcolor: transparent"]CO2 With Light[/TD]
[TD="bgcolor: transparent"] S6[/TD]
[TD="bgcolor: transparent"]2/26/2019[/TD]
[TD="bgcolor: transparent"] Aedes[/TD]
[TD="bgcolor: transparent"]vexans[/TD]
[TD="bgcolor: transparent"]8[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]UV Light Trap[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]New Jersey Trap[/TD]
[TD="bgcolor: transparent"]Light[/TD]
[TD="bgcolor: transparent"] S1[/TD]
[TD="bgcolor: transparent"]3/1/2019[/TD]
[TD="bgcolor: transparent"] Aedes[/TD]
[TD="bgcolor: transparent"]vexans[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Light Trap[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]UV Light Trap[/TD]
[TD="bgcolor: transparent"]CO2 With Light[/TD]
[TD="bgcolor: transparent"] S1[/TD]
[TD="bgcolor: transparent"]3/1/2019[/TD]
[TD="bgcolor: transparent"]Aedes[/TD]
[TD="bgcolor: transparent"]vexans[/TD]
[TD="bgcolor: transparent"]9[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Ovicup/Ovitrap[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="width: 567, bgcolor: transparent, colspan: 7"]What I would like to do is count the frequency a trap runs between two dates. There are multiple variables, A (Trap Type), B (Attractants), C (Trap ID) and D (Date). The trap may catch more than one species in a single night and therefore have multiple entries. I need to convert multiple entries on the same date, with the same variables to a "1" and not "15" if I catch 15 different species in a single trap. Above, S1 trap should only read "4" instead of "6", because it only ran on 4 nights with the UV light trap. It should only read "1" if you change the trap type to "New Jersey Trap". I can run the total on mosquitoes captured easily enough with SUMIFS, but FREQUENCY has me whooped (I just don't understand it well enough).

[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Attractant
CO2 With Light
Light
Octenol

[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, colspan: 3"]UV Light Trap[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Trap site[/TD]
[TD="bgcolor: transparent"]Nights Run[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]S1[/TD]
[TD="bgcolor: transparent"]??[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]S2[/TD]
[TD="bgcolor: transparent"]??[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]S3[/TD]
[TD="bgcolor: transparent"]??[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]S4[/TD]
[TD="bgcolor: transparent"]??[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]S5[/TD]
[TD="bgcolor: transparent"]??[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]S6[/TD]
[TD="bgcolor: transparent"]??[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Sort by date Sort by votes
Re: Help with frequency please

I'm not entirely sure I understand your requirements, but try this out:

Excel 2012
ABCDEFGHIJ
Collection Method/ Trap TypeAttractant(s) UsedID for Collection LocationTrap Pickup DateGenusSpecies# Females Collected
UV Light TrapCO2 With LightS1Aedesvexans
UV Light TrapCO2 With LightS6AedessollicitansBegin date:
UV Light TrapCO2 With LightS1PsorophoraferoxEnd date:
UV Light TrapCO2 With LightS1Aedesvexans
UV Light TrapCO2 With LightS6AedesvexansTrap Types
UV Light TrapCO2 With LightS1AedesvexansNew Jersey Trap
UV Light TrapCO2 With LightS6AedesvexansUV Light Trap
New Jersey TrapLightS1AedesvexansLight Trap
UV Light TrapCO2 With LightS1AedesvexansOvicup/Ovitrap
UV Light Trap
Trap siteNights Run
S1
S2
S3
S4
S5
S6

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]2/12/2019[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]2/12/2019[/TD]

[TD="align: right"]50[/TD]
[TD="align: right"][/TD]

[TD="align: right"]12-Feb[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]2/12/2019[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1-Mar[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]2/13/2019[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]2/13/2019[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]2/26/2019[/TD]

[TD="align: right"]6[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]2/26/2019[/TD]

[TD="align: right"]8[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]3/1/2019[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]3/1/2019[/TD]

[TD="align: right"]9[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet9​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B17[/TH]
[TD="align: left"]{=SUM(SIGN(FREQUENCY(IF($A$2:$A$10=$A$15,IF($C$2:$C$10=A17,$D$2:$D$10)),$D$2:$D$10)))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Re: Help with frequency please

Eric W said:
I'm not entirely sure I understand your requirements, but try this out:

Excel 2012
ABCDEFGHIJ
Collection Method/ Trap TypeAttractant(s) UsedID for Collection LocationTrap Pickup DateGenusSpecies# Females Collected
UV Light TrapCO2 With LightS1Aedesvexans
UV Light TrapCO2 With LightS6AedessollicitansBegin date:
UV Light TrapCO2 With LightS1PsorophoraferoxEnd date:
UV Light TrapCO2 With LightS1Aedesvexans
UV Light TrapCO2 With LightS6AedesvexansTrap Types
UV Light TrapCO2 With LightS1AedesvexansNew Jersey Trap
UV Light TrapCO2 With LightS6AedesvexansUV Light Trap
New Jersey TrapLightS1AedesvexansLight Trap
UV Light TrapCO2 With LightS1AedesvexansOvicup/Ovitrap
UV Light Trap
Trap siteNights Run
S1
S2
S3
S4
S5
S6

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]2/12/2019[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]2/12/2019[/TD]

[TD="align: right"]50[/TD]
[TD="align: right"][/TD]

[TD="align: right"]12-Feb[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]2/12/2019[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"][/TD]

[TD="align: right"]15-Feb
[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]2/13/2019[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]2/13/2019[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]2/26/2019[/TD]

[TD="align: right"]6[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]2/26/2019[/TD]

[TD="align: right"]8[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]3/1/2019[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]3/1/2019[/TD]

[TD="align: right"]9[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet9​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]B17[/TH]
[TD="align: left"]{=SUM(SIGN(FREQUENCY(IF($A$2:$A$10=$A$15,IF($C$2:$C$10=A17,$D$2:$D$10)),$D$2:$D$10)))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
That works almost perfectly, thank you. Just one additional request. I will need to pull data out between two dates. The example is only a few lines long, the actual spreadsheet will span an entire year and will have hundreds of entries. I will be extracting the numbers for weekly and monthly reports. I will have the beginning date and the ending date of the time period in question (J3 & J4) and will want to sum everything between (<= and >=) those dates. I really appreciate your help, I toiled over this several hours and couldn't wrap my mind around how to write it. I actually had SUM, SIGN and FREQUENCY in mine, just couldn't
correctly ​
tell Excel what I needed it to do.
 
Upvote 0
Re: Help with frequency please

It appears you can just use a SUMIFS formula:

Excel 2012
ABCDEFGHIJ
Collection Method/ Trap TypeAttractant(s) UsedID for Collection LocationTrap Pickup DateGenusSpecies# Females Collected
UV Light TrapCO2 With LightS1Aedesvexans
UV Light TrapCO2 With LightS6AedessollicitansBegin date:
UV Light TrapCO2 With LightS1PsorophoraferoxEnd date:
UV Light TrapCO2 With LightS1Aedesvexans
UV Light TrapCO2 With LightS6AedesvexansTrap Types
UV Light TrapCO2 With LightS1AedesvexansNew Jersey Trap
UV Light TrapCO2 With LightS6AedesvexansUV Light Trap
New Jersey TrapLightS1AedesvexansLight Trap
UV Light TrapCO2 With LightS1AedesvexansOvicup/Ovitrap
UV Light Trap
Trap siteNights RunSum of females
S1
S2
S3
S4
S5
S6

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]2/12/2019[/TD]

[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]2/12/2019[/TD]

[TD="align: right"]50[/TD]
[TD="align: right"][/TD]

[TD="align: right"]12-Feb[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]2/12/2019[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1-Mar[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]2/13/2019[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]2/13/2019[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]2/26/2019[/TD]

[TD="align: right"]6[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]2/26/2019[/TD]

[TD="align: right"]8[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]3/1/2019[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]3/1/2019[/TD]

[TD="align: right"]9[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]62[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet9​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C17[/TH]
[TD="align: left"]=SUMIFS($G$2:$G$10,$A$2:$A$10,$A$15,$C$2:$C$10,A17,$D$2:$D$10,">="&$J$3,$D$2:$D$10,"<="&$J$4)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B17[/TH]
[TD="align: left"]{=SUM(SIGN(FREQUENCY(IF($A$2:$A$10=$A$15,IF($C$2:$C$10=A17,IF($D$2:$D$10>=$J$3,IF($D$2:$D$10<=$J$4,$D$2:$D$10)))),$D$2:$D$10)))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



I also noticed that my original formula doesn't restrict by the dates, so I add a check for that too.
 
Upvote 0
Re: Help with frequency please

Perfect! I can make everything the same date and the same trap site and it still totals up as "1". That is exactly what I needed it to do. The SUM formula you put in should work also with a few changes. I have 62 species it has to sort through. I add a new column on that puts the genus and species together (=E2&" "&F2) and sum according to the column header in the report table. Thanks for the help!
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
570
smozgur
smozgur
E
  • Question Question
Can anyone help me get my VBA code working? I have spent tons of time but can't quite fix it.
Replies
3
Views
3K
profray
P
J
  • Question Question
pulling data from one chart to another
Replies
1
Views
811
jb00976
J
E
  • Question Question
Yearfrac problem
Replies
3
Views
5K
EleanorSykes1989
E
G
  • Question Question
Conditional formating where there are 2 values in a cell
Replies
3
Views
1K
Godders199
G

Forum statistics

Threads
1,223,896
Messages
6,175,262
Members
452,627
Latest member
KitkatToby

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top