How To Simplify Formula with SUMIFs

  • Thread starter Thread starter Han
  • Start date Start date
H

Han

Code
Double Feature Senior
Pass Redemption Hubble
COMP- staff
Double Feature General
IMAX- General
Pass Redemption Hubble
COMP
Companion Gen
Companion Snr
Double Feature Child
Double Feature General
IMAX- Child*
IMAX- Child*
IMAX- General
IMAX- General
IMAX- Senior
IMAX- Senior
Pass Redemption Hubble
Pass Redemption Hubble
2 for 1 DF- General
2 for 1- General
COMP
Companion DF Gen
Csc- Corporate Single $5
Daycare- Adult
Daycare- Child
IMAX- Child*
IMAX- Child*
IMAX- General
IMAX- General
Pass Redemption Hubble
COMP
Companion DF Chd
Double Feature Child
Double Feature General
Double Feature General
Double Feature Senior
Double Feature Senior
IMAX- Child*
IMAX- Child*
IMAX- General
IMAX- General
IMAX- Senior
IMAX- Senior
Pass Redemption Hubble
Double Feature Child
Double Feature General
Double Feature Senior
IMAX- Child*
IMAX- General
IMAX- General
Pass Redemption Hubble
2 for 1- General
Companion Gen
Double Feature Child
Double Feature General
IMAX- Child*
IMAX- General
IMAX- General
IMAX- Senior
Pass Redemption Hubble
Pass Redemption Hubble
Companion Gen
Double Feature Child
Double Feature Child
Double Feature General
Double Feature General
IMAX- Child*
IMAX- Child*
IMAX- General
IMAX- General
IMAX- Senior
IMAX- Senior
Pass Redemption Hubble
Pass Redemption Hubble
COMP
Companion Gen
Double Feature General
IMAX- Child*
IMAX- General
IMAX- General
IMAX- Senior
Pass Redemption Hubble
Double Feature Child
Double Feature General
IMAX- Child*
IMAX- General
Pass Redemption Hubble
Companion Gen
IMAX- Child*
IMAX- General
Pass Redemption Hubble
Csc- Corporate Single $5
Double Feature Child
Double Feature General
Double Feature Senior
IMAX- Child*
IMAX- General
IMAX- Senior
Pass Redemption Hubble
COMP
IMAX- General
Pass Redemption Hubble
COMP
COMP- staff
Companion Snr
Double Feature Child
Double Feature General
Double Feature Senior
IMAX- Child*
IMAX- General
IMAX- Senior
Pass Redemption Hubble
COMP
Companion Snr
IMAX- Child*
IMAX- General
Pass Redemption Hubble
2 for 1 DF- Child
Double Feature Child
Double Feature General
Double Feature Senior
Group DF- Adult
Group DF- CHD, SNR, YTH
IMAX- Child*
IMAX- General
Pass Redemption Hubble
IMAX- General
COMP
Double Feature General
Group DF- Adult
Group DF- CHD, SNR, YTH
IMAX- Child*
IMAX- General
IMAX- Senior
Pass Redemption Hubble
Companion Gen
Companion Snr
IMAX- Child*
IMAX- General
Lineup D&M Sf
Pass Redemption Hubble
$1 Off- Child
$1 Off- General
$1 Off- Senior
Double Feature General
IMAX- Child*
IMAX- General
IMAX- Senior
Pass Redemption Hubble
COMP
IMAX- Child*
IMAX- General
Companion DF Snr
Double Feature General
IMAX- Child*
IMAX- General
IMAX- Senior
Pass Redemption Hubble
Companion Gen
Double Feature General
IMAX- General
IMAX- Senior
Pass Redemption Hubble
2 for 1 DF- General
Companion DF Chd
Companion DF Gen
Double Feature General
Double Feature Senior
IMAX- Child*
IMAX- General
IMAX- Senior
Pass Redemption Hubble
IMAX- General
2 for 1- General
Double Feature General
Education-COMP
Group DF- Adult
Group DF- CHD, SNR, YTH
IMAX- Child*
IMAX- General
IMAX- Senior
Pass Redemption Hubble
Double Feature Child
Double Feature General
IMAX- Child*
IMAX- General
Pass Redemption Hubble
COMP
Double Feature General
IMAX- Child*
IMAX- General
IMAX- Senior
Double Feature General
Education-COMP
Group DF- Adult
IMAX- Child*
IMAX- General
Pass Redemption Hubble
Double Feature General
IMAX- Child*
IMAX- General
Companion Snr
Double Feature General
IMAX- General
IMAX- Senior
Pass Redemption Hubble
Companion Gen
Double Feature General
IMAX- General
Pass Redemption Hubble
Double Feature Child
Double Feature General
Double Feature Senior
IMAX- Child*
IMAX- General
IMAX- Senior
Pass Redemption Hubble
IMAX- General
Companion DF Gen
Double Feature General
IMAX- Child*
IMAX- General
IMAX- Senior
Lineup D&M Df
Pass Redemption Hubble
COMP
Double Feature Senior
IMAX- Child*
IMAX- General
IMAX- Senior
Lineup D&M Sf
2 for 1 DF- General
COMP
Double Feature Child
Double Feature General
IMAX- Child*
IMAX- General
Pass Redemption Hubble
IMAX- General
Pass Redemption Hubble
Companion Child
Companion Gen
Double Feature Child
Double Feature General
Double Feature Senior
Education-COMP
Group DF- CHD, SNR, YTH
Group- IMAX- Adult
Group- Imax- Child/Youth
IMAX- Child*
IMAX- General
IMAX- Senior
Pass Redemption Hubble
COMP- staff
Double Feature General
IMAX- General
IMAX- Senior
Double Feature Child
Double Feature General
IMAX- Child*
IMAX- General
IMAX- Senior
Companion DF Gen
IMAX- General
Pass Redemption Hubble
Companion Gen
Double Feature Child
Double Feature General
Group DF- Adult
IMAX- Child*
IMAX- General
IMAX- Senior
Pass Redemption Hubble
Double Feature General
Double Feature Senior
IMAX- Child*
IMAX- General
Pass Redemption Hubble
Companion DF Snr
Daycare- Adult
Daycare- Child
Daycare- COMP
Double Feature General
IMAX- General
IMAX- Senior
Pass Redemption Hubble
Double Feature General
IMAX- Child*
IMAX- General
Lineup D&M Sf
Pass Redemption Hubble
Double Feature Child
Double Feature General
Double Feature Senior
IMAX- Child*
IMAX- General
Pass Redemption Hubble
Companion Gen
Double Feature General
Double Feature Senior
IMAX- Child*
IMAX- General
IMAX- Senior
Pass Redemption Hubble
Birthday
Birthday- COMP
Companion Child
Double Feature General
IMAX- Child*
IMAX- General
IMAX- Senior
Pass Redemption Hubble
Companion Gen
Double Feature General
IMAX- Child*
IMAX- General
IMAX- Senior
Pass Redemption Hubble
Double Feature Child
Double Feature General
Double Feature Senior
IMAX- Child*
IMAX- General
IMAX- Senior
Pass Redemption Hubble
Double Feature Child
Double Feature General
IMAX- Child*
IMAX- General
Companion Child
Double Feature Child
Double Feature General
Double Feature Senior
IMAX- Child*
IMAX- General
IMAX- Senior
Pass Redemption Hubble
Companion DF Chd
Double Feature Child
Double Feature General
IMAX- Child*
IMAX- General
Pass Redemption Hubble

=======================


As the listing shows, there are numerous COMPs such as COMP- staff,
Education- COMP, Birthday- COMP, COMP and more......, and I want to
get the total count of COMPS. Following Peter's great suggestion, I
wrote the following formula ():


==SUMIF(E2:E345,"COMP",F2:F345)+ SUMIF(E2:E345,"COMP- staff", F2:F345)
+ SUMIF(E2:E345,"Daycare- COMP", F2:F345)+ SUMIF(E2:E345,"Birthday-
COMP", F2:F345), and I got the right count. but I think the formula is
too long and time -consuming when there are many kinds of COMPs. Could
you please tell me how to simplify the formular?


Your response will be appreciated.


Han Chen
 
Hi Han

Just responded to your earlier posting in the original thread
=SUMIF(E2:E345,"*COMP*",F2:F345)

--

Regards
Roger Govier

Han said:
Code
Double Feature Senior
Pass Redemption Hubble
COMP- staff
Double Feature General
IMAX- General
Pass Redemption Hubble
COMP
Companion Gen
Companion Snr
Double Feature Child
Double Feature General
IMAX- Child*
IMAX- Child*
IMAX- General
IMAX- General
IMAX- Senior
IMAX- Senior
Pass Redemption Hubble
Pass Redemption Hubble
2 for 1 DF- General
2 for 1- General
COMP
Companion DF Gen
Csc- Corporate Single $5
Daycare- Adult
Daycare- Child
IMAX- Child*
IMAX- Child*
IMAX- General
IMAX- General
Pass Redemption Hubble
COMP
Companion DF Chd
Double Feature Child
Double Feature General
Double Feature General
Double Feature Senior
Double Feature Senior
IMAX- Child*
IMAX- Child*
IMAX- General
IMAX- General
IMAX- Senior
IMAX- Senior
Pass Redemption Hubble
Double Feature Child
Double Feature General
Double Feature Senior
IMAX- Child*
IMAX- General
IMAX- General
Pass Redemption Hubble
2 for 1- General
Companion Gen
Double Feature Child
Double Feature General
IMAX- Child*
IMAX- General
IMAX- General
IMAX- Senior
Pass Redemption Hubble
Pass Redemption Hubble
Companion Gen
Double Feature Child
Double Feature Child
Double Feature General
Double Feature General
IMAX- Child*
IMAX- Child*
IMAX- General
IMAX- General
IMAX- Senior
IMAX- Senior
Pass Redemption Hubble
Pass Redemption Hubble
COMP
Companion Gen
Double Feature General
IMAX- Child*
IMAX- General
IMAX- General
IMAX- Senior
Pass Redemption Hubble
Double Feature Child
Double Feature General
IMAX- Child*
IMAX- General
Pass Redemption Hubble
Companion Gen
IMAX- Child*
IMAX- General
Pass Redemption Hubble
Csc- Corporate Single $5
Double Feature Child
Double Feature General
Double Feature Senior
IMAX- Child*
IMAX- General
IMAX- Senior
Pass Redemption Hubble
COMP
IMAX- General
Pass Redemption Hubble
COMP
COMP- staff
Companion Snr
Double Feature Child
Double Feature General
Double Feature Senior
IMAX- Child*
IMAX- General
IMAX- Senior
Pass Redemption Hubble
COMP
Companion Snr
IMAX- Child*
IMAX- General
Pass Redemption Hubble
2 for 1 DF- Child
Double Feature Child
Double Feature General
Double Feature Senior
Group DF- Adult
Group DF- CHD, SNR, YTH
IMAX- Child*
IMAX- General
Pass Redemption Hubble
IMAX- General
COMP
Double Feature General
Group DF- Adult
Group DF- CHD, SNR, YTH
IMAX- Child*
IMAX- General
IMAX- Senior
Pass Redemption Hubble
Companion Gen
Companion Snr
IMAX- Child*
IMAX- General
Lineup D&M Sf
Pass Redemption Hubble
$1 Off- Child
$1 Off- General
$1 Off- Senior
Double Feature General
IMAX- Child*
IMAX- General
IMAX- Senior
Pass Redemption Hubble
COMP
IMAX- Child*
IMAX- General
Companion DF Snr
Double Feature General
IMAX- Child*
IMAX- General
IMAX- Senior
Pass Redemption Hubble
Companion Gen
Double Feature General
IMAX- General
IMAX- Senior
Pass Redemption Hubble
2 for 1 DF- General
Companion DF Chd
Companion DF Gen
Double Feature General
Double Feature Senior
IMAX- Child*
IMAX- General
IMAX- Senior
Pass Redemption Hubble
IMAX- General
2 for 1- General
Double Feature General
Education-COMP
Group DF- Adult
Group DF- CHD, SNR, YTH
IMAX- Child*
IMAX- General
IMAX- Senior
Pass Redemption Hubble
Double Feature Child
Double Feature General
IMAX- Child*
IMAX- General
Pass Redemption Hubble
COMP
Double Feature General
IMAX- Child*
IMAX- General
IMAX- Senior
Double Feature General
Education-COMP
Group DF- Adult
IMAX- Child*
IMAX- General
Pass Redemption Hubble
Double Feature General
IMAX- Child*
IMAX- General
Companion Snr
Double Feature General
IMAX- General
IMAX- Senior
Pass Redemption Hubble
Companion Gen
Double Feature General
IMAX- General
Pass Redemption Hubble
Double Feature Child
Double Feature General
Double Feature Senior
IMAX- Child*
IMAX- General
IMAX- Senior
Pass Redemption Hubble
IMAX- General
Companion DF Gen
Double Feature General
IMAX- Child*
IMAX- General
IMAX- Senior
Lineup D&M Df
Pass Redemption Hubble
COMP
Double Feature Senior
IMAX- Child*
IMAX- General
IMAX- Senior
Lineup D&M Sf
2 for 1 DF- General
COMP
Double Feature Child
Double Feature General
IMAX- Child*
IMAX- General
Pass Redemption Hubble
IMAX- General
Pass Redemption Hubble
Companion Child
Companion Gen
Double Feature Child
Double Feature General
Double Feature Senior
Education-COMP
Group DF- CHD, SNR, YTH
Group- IMAX- Adult
Group- Imax- Child/Youth
IMAX- Child*
IMAX- General
IMAX- Senior
Pass Redemption Hubble
COMP- staff
Double Feature General
IMAX- General
IMAX- Senior
Double Feature Child
Double Feature General
IMAX- Child*
IMAX- General
IMAX- Senior
Companion DF Gen
IMAX- General
Pass Redemption Hubble
Companion Gen
Double Feature Child
Double Feature General
Group DF- Adult
IMAX- Child*
IMAX- General
IMAX- Senior
Pass Redemption Hubble
Double Feature General
Double Feature Senior
IMAX- Child*
IMAX- General
Pass Redemption Hubble
Companion DF Snr
Daycare- Adult
Daycare- Child
Daycare- COMP
Double Feature General
IMAX- General
IMAX- Senior
Pass Redemption Hubble
Double Feature General
IMAX- Child*
IMAX- General
Lineup D&M Sf
Pass Redemption Hubble
Double Feature Child
Double Feature General
Double Feature Senior
IMAX- Child*
IMAX- General
Pass Redemption Hubble
Companion Gen
Double Feature General
Double Feature Senior
IMAX- Child*
IMAX- General
IMAX- Senior
Pass Redemption Hubble
Birthday
Birthday- COMP
Companion Child
Double Feature General
IMAX- Child*
IMAX- General
IMAX- Senior
Pass Redemption Hubble
Companion Gen
Double Feature General
IMAX- Child*
IMAX- General
IMAX- Senior
Pass Redemption Hubble
Double Feature Child
Double Feature General
Double Feature Senior
IMAX- Child*
IMAX- General
IMAX- Senior
Pass Redemption Hubble
Double Feature Child
Double Feature General
IMAX- Child*
IMAX- General
Companion Child
Double Feature Child
Double Feature General
Double Feature Senior
IMAX- Child*
IMAX- General
IMAX- Senior
Pass Redemption Hubble
Companion DF Chd
Double Feature Child
Double Feature General
IMAX- Child*
IMAX- General
Pass Redemption Hubble

=======================


As the listing shows, there are numerous COMPs such as COMP- staff,
Education- COMP, Birthday- COMP, COMP and more......, and I want to
get the total count of COMPS. Following Peter's great suggestion, I
wrote the following formula ():


==SUMIF(E2:E345,"COMP",F2:F345)+ SUMIF(E2:E345,"COMP- staff", F2:F345)
+ SUMIF(E2:E345,"Daycare- COMP", F2:F345)+ SUMIF(E2:E345,"Birthday-
COMP", F2:F345), and I got the right count. but I think the formula is
too long and time -consuming when there are many kinds of COMPs. Could
you please tell me how to simplify the formular?


Your response will be appreciated.


Han Chen



__________ Information from ESET Smart Security, version of virus
signature database 5430 (20100907) __________

The message was checked by ESET Smart Security.

http://www.eset.com
Click to expand...

__________ Information from ESET Smart Security, version of virus signature database 5430 (20100907) __________

The message was checked by ESET Smart Security.

http://www.eset.com
 
Yes, but see my caveat.

Pete

Hi Han

Just responded to your earlier posting in the original thread
=SUMIF(E2:E345,"*COMP*",F2:F345)

--

Regards
Roger Govier














__________ Information from ESET Smart Security, version of virus signature database 5430 (20100907) __________

The message was checked by ESET Smart Security.

http://www.eset.com- Hide quoted text -

- Show quoted text -
Click to expand...
 
Back
Top