How To Simplify Formula with SUMIFs

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
 
R

Roger Govier

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
 
P

Pete_UK

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SUMIF 9

Top