Frequency

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.
Collection Method/ Trap TypeAttractant(s) UsedID for Collection LocationTrap Pickup DateGenusSpecies# Females Collected
UV Light TrapCO2 With Light S12/12/2019 Aedesvexans10
UV Light TrapCO2 With Light S62/12/2019 Aedessollicitans50Begin date:12-Feb
UV Light TrapCO2 With Light S12/12/2019 Psorophoraferox5End date:1-Mar
UV Light TrapCO2 With Light S12/13/2019 Aedesvexans2
UV Light TrapCO2 With Light S62/13/2019 Aedesvexans4Trap Types
UV Light TrapCO2 With Light S12/26/2019 Aedesvexans6New Jersey Trap

UV Light TrapCO2 With Light S62/26/2019 Aedesvexans8UV Light Trap
New Jersey TrapLight S13/1/2019 Aedesvexans1Light Trap
UV Light TrapCO2 With Light S13/1/2019Aedesvexans9Ovicup/Ovitrap
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).

Attractant
CO2 With Light
Light
Octenol

UV Light Trap
Trap siteNights Run
S1??
S2??
S3??
S4??
S5??
S6??
<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> </tbody>
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,214
Re: Help with frequency please

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

Excel 2012
ABCDEFGHIJ
1Collection Method/ Trap TypeAttractant(s) UsedID for Collection LocationTrap Pickup DateGenusSpecies# Females Collected
2UV Light TrapCO2 With LightS12/12/2019Aedesvexans10
3UV Light TrapCO2 With LightS62/12/2019Aedessollicitans50Begin date:12-Feb
4UV Light TrapCO2 With LightS12/12/2019Psorophoraferox5End date:1-Mar
5UV Light TrapCO2 With LightS12/13/2019Aedesvexans2
6UV Light TrapCO2 With LightS62/13/2019Aedesvexans4Trap Types
7UV Light TrapCO2 With LightS12/26/2019Aedesvexans6New Jersey Trap
8UV Light TrapCO2 With LightS62/26/2019Aedesvexans8UV Light Trap
9New Jersey TrapLightS13/1/2019Aedesvexans1Light Trap
10UV Light TrapCO2 With LightS13/1/2019Aedesvexans9Ovicup/Ovitrap
11
12
13
14
15UV Light Trap
16Trap siteNights Run
17S14
18S20
19S30
20S40
21S50
22S63

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet9

Array Formulas
CellFormula
B17{=SUM(SIGN(FREQUENCY(IF($A$2:$A$10=$A$15,IF($C$2:$C$10=A17,$D$2:$D$10)),$D$2:$D$10)))}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>
 

Pefird

New Member
Joined
Mar 15, 2019
Messages
5
Re: Help with frequency please

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

Excel 2012
ABCDEFGHIJ
1Collection Method/ Trap TypeAttractant(s) UsedID for Collection LocationTrap Pickup DateGenusSpecies# Females Collected
2UV Light TrapCO2 With LightS12/12/2019Aedesvexans10
3UV Light TrapCO2 With LightS62/12/2019Aedessollicitans50Begin date:12-Feb
4UV Light TrapCO2 With LightS12/12/2019Psorophoraferox5End date:15-Feb
5UV Light TrapCO2 With LightS12/13/2019Aedesvexans2
6UV Light TrapCO2 With LightS62/13/2019Aedesvexans4Trap Types
7UV Light TrapCO2 With LightS12/26/2019Aedesvexans6New Jersey Trap
8UV Light TrapCO2 With LightS62/26/2019Aedesvexans8UV Light Trap
9New Jersey TrapLightS13/1/2019Aedesvexans1Light Trap
10UV Light TrapCO2 With LightS13/1/2019Aedesvexans9Ovicup/Ovitrap
11
12
13
14
15UV Light Trap
16Trap siteNights Run
17S14
18S20
19S30
20S40
21S50
22S63

<tbody>
</tbody>
Sheet9

Array Formulas
CellFormula
B17{=SUM(SIGN(FREQUENCY(IF($A$2:$A$10=$A$15,IF($C$2:$C$10=A17,$D$2:$D$10)),$D$2:$D$10)))}

<tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>
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.
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,214
Re: Help with frequency please

It appears you can just use a SUMIFS formula:

Excel 2012
ABCDEFGHIJ
1Collection Method/ Trap TypeAttractant(s) UsedID for Collection LocationTrap Pickup DateGenusSpecies# Females Collected
2UV Light TrapCO2 With LightS12/12/2019Aedesvexans10
3UV Light TrapCO2 With LightS62/12/2019Aedessollicitans50Begin date:12-Feb
4UV Light TrapCO2 With LightS12/12/2019Psorophoraferox5End date:1-Mar
5UV Light TrapCO2 With LightS12/13/2019Aedesvexans2
6UV Light TrapCO2 With LightS62/13/2019Aedesvexans4Trap Types
7UV Light TrapCO2 With LightS12/26/2019Aedesvexans6New Jersey Trap
8UV Light TrapCO2 With LightS62/26/2019Aedesvexans8UV Light Trap
9New Jersey TrapLightS13/1/2019Aedesvexans1Light Trap
10UV Light TrapCO2 With LightS13/1/2019Aedesvexans9Ovicup/Ovitrap
11
12
13
14
15UV Light Trap
16Trap siteNights RunSum of females
17S1432
18S200
19S300
20S400
21S500
22S6362

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet9

Worksheet Formulas
CellFormula
C17=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)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
B17{=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)))}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>



I also noticed that my original formula doesn't restrict by the dates, so I add a check for that too.
 

Pefird

New Member
Joined
Mar 15, 2019
Messages
5
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,108,708
Messages
5,524,431
Members
409,577
Latest member
Dwg

This Week's Hot Topics

Top