SUMIF

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

Han

Manually we can get the count of “COMP” 16(see listing below), but
result of formula [=SUMIF (E2:E39,"COMP", F2:F39)] is 16, could you
help me fix the formula? Thanks for your help!

Dmr Child
Dmr General
Pass Redemption Dmr
Dmr Child
Dmr General
Companion Dmr Gen
Dmr General
Pass Redemption Dmr
COMP
Dmr Child
Dmr General
Pass Redemption Dmr
Dmr General
Pass Redemption Dmr
Companion Dmr Gen
Dmr General
Pass Redemption Dmr
Companion Dmr Chd/Snr
Dmr Child
Dmr General
Dmr Senior
Pass Redemption Dmr
COMP
COMP- staff
Companion Dmr Gen
Dmr Child
Dmr General
Pass Redemption Dmr
Dmr Child
Dmr General
Dmr Senior
Pass Redemption Dmr
COMP
Companion Dmr Gen
Dmr 2 for 1 Gen
Dmr Child
Dmr General
Pass Redemption Dmr
 
Manually we can get the count of “COMP” 24(see listing below), but
result of formula [=SUMIF (E2:E39,"COMP", F2:F39)] is 16, could you
help me fix the formula? Thanks for your help!
 
Hi Han

It all depends what is in column F.
Sumif is giving you the Sum of values in column F, where column E = COMP
If there are some cells in column F with zero, then it is quite possible
that Count could be 24 and Sumif return 16.

--

Regards
Roger Govier

Han said:
Manually we can get the count of “COMP” 24(see listing below), but
result of formula [=SUMIF (E2:E39,"COMP", F2:F39)] is 16, could you
help me fix the formula? Thanks for your help!

__________ Information from ESET Smart Security, version of virus
signature database 5418 (20100902) __________

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 5418 (20100902) __________

The message was checked by ESET Smart Security.

http://www.eset.com
 
Can we find a way which just count COMP or COMP -XX but not COMPanion?
Thanks for your reply anyway!



-------------------------------------------------------------------------------------------------------------------------------------------------------------
Hi Han

It all depends what is in column F.
Sumif is giving you the Sum of values in column F, where column E = COMP
If there are some cells in column F with zero, then it is quite possible
that Count could be 24 and Sumif return 16.

--

Regards
Roger Govier


Manually we can get the count of “COMP” 24(see listing below), but
result of formula [=SUMIF (E2:E39,"COMP", F2:F39)] is 16, could you
help me fix the formula? Thanks for your help!
Click to expand...
__________ Information from ESET Smart Security, version of virus
signature database 5418 (20100902) __________
Click to expand...
The message was checked by ESET Smart Security.
Click to expand...
http://www.eset.com
Click to expand...

__________ Information from ESET Smart Security, version of virus signature database 5418 (20100902) __________

The message was checked by ESET Smart Security.

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

=COUNTIF(E2:E39,"COMP") + COUNTIF(E2:E39,"COMP -XX")

Hope this helps.

Pete

Can we find a way which just count COMP or COMP -XX but not COMPanion?
Thanks for your reply anyway!

---------------------------------------------------------------------------­---------------------------------------------------------------------------­-------
Hi Han
Click to expand...
It all depends what is in column F.
Sumif is giving you the Sum of values in column F, where column E = COMP
If there are some cells in column F with zero, then it is quite possible
that Count could be 24 and Sumif return 16.

Regards
Roger Govier
Click to expand...
news:313f8b1b-ae3c-4c1b-a990-4c10cb107c9d@a17g2000yqi.googlegroups.com....
Click to expand...
Manually we can get the count of “COMP” 24(see listing below), but
result of formula [=SUMIF (E2:E39,"COMP", F2:F39)] is 16, could you
help me fix the formula? Thanks for your help!
__________ Information from ESET Smart Security, version of virus
signature database 5418 (20100902) __________
The message was checked by ESET Smart Security.
http://www.eset.com
Click to expand...
Click to expand...
__________ Information from ESET Smart Security, version of virus signature database 5418 (20100902) __________
Click to expand...
The message was checked by ESET Smart Security.
Click to expand...
http://www.eset.com- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...
 
It works, Thank you very much!





Try this:

=COUNTIF(E2:E39,"COMP") + COUNTIF(E2:E39,"COMP -XX")

Hope this helps.

Pete

Can we find a way which just count COMP or COMP -XX but not COMPanion?
Thanks for your reply anyway!
Click to expand...
---------------------------------------------------------------------------­­--------------------------------------------------------------------------­-­-------
On Sep 2, 12:20 pm, "Roger Govier" <[email protected]>
wrote:
Hi Han
It all depends what is in column F.
Sumif is giving you the Sum of values in column F, where column E =COMP
If there are some cells in column F with zero, then it is quite possible
that Count could be 24 and Sumif return 16.
--
Regards
Roger Govier

Manually we can get the count of “COMP” 24(see listing below), but
result of formula [=SUMIF (E2:E39,"COMP", F2:F39)] is 16, could you
help me fix the formula? Thanks for your help!
__________ Information from ESET Smart Security, version of virus
signature database 5418 (20100902) __________
The message was checked by ESET Smart Security.
http://www.eset.com
__________ Information from ESET Smart Security, version of virus signature database 5418 (20100902) __________
The message was checked by ESET Smart Security.
http://www.eset.com-Hide quoted text -
Click to expand...
Click to expand...
- Show quoted text -- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...
 
You're welcome, Han - thanks for feeding back.

Pete

It works, Thank you very much!

Try this:
Click to expand...
=COUNTIF(E2:E39,"COMP") + COUNTIF(E2:E39,"COMP -XX")
Click to expand...
Hope this helps.
Pete
Click to expand...

Can we find a way which just count COMP or COMP -XX but not COMPanion?
Thanks for your reply anyway!
---------------------------------------------------------------------------­­­-------------------------------------------------------------------------­-­-­-------
On Sep 2, 12:20 pm, "Roger Govier" <[email protected]>
wrote:
Hi Han
It all depends what is in column F.
Sumif is giving you the Sum of values in column F, where column E = COMP
If there are some cells in column F with zero, then it is quite possible
that Count could be 24 and Sumif return 16.
--
Regards
Roger Govier

Manually we can get the count of “COMP” 24(see listing below), but
result of formula [=SUMIF (E2:E39,"COMP", F2:F39)] is 16, couldyou
help me fix the formula? Thanks for your help!
__________ Information from ESET Smart Security, version of virus
signature database 5418 (20100902) __________
The message was checked by ESET Smart Security.
http://www.eset.com
__________ Information from ESET Smart Security, version of virus signature database 5418 (20100902) __________
The message was checked by ESET Smart Security.
http://www.eset.com-Hidequoted text -
- Show quoted text -- Hide quoted text -
Click to expand...
Click to expand...
- Show quoted text -- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...
 
Correct formula is

=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) + SUMIF(E2:E345,"Education-COMP",F2:F345)

Correct Count is 64.
 
Hi Han

Try
=SUMIF(E2:E345,"*COMP*",F2:F345)

--

Regards
Roger Govier

Han said:
Correct formula is

=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) + SUMIF(E2:E345,"Education-COMP",F2:F345)

Correct Count is 64.




__________ 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
 
Roger,

there are some entries in the list which have the word Companion, and
Han doesn't seem to want to include these in the sum. So, I would
suggest:

=SUMIF(E2:E345,"*COMP*",F2:F345) -
SUMIF(E2:E345,"*Companion*",F2:F345)

There might be other words in there containing comp, but It's too long
a list for me to scan through accurately.

Han:

you can use wildcard characters with SUMIF, as the formula shows.

Hope this helps.

Pete
 
Back
Top