SUMIF problemwith 2 column range

[lindasf]

Hi,

The text “Eligibility Supervisor” occurs in both Columns B and C withi
the range B2:C16, yet the SUMIF in cell B25 only counts one occurrenc
– the one in cell B16 I presume.

If I change the range to B2:B16 or C2:C16 it will count either 1 or 3
respectively (only those occurrences in that specific column).

TIA for your help.


File attache

Attachment filename: medi-cal training analysis-demo.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=58518

 

[RagDyer]

I don't open attatchments, so perhaps that's why I don't follow you saying
<<"SUMIF in cell B25 only counts one occurrence">>.

You have SUMIF *counting* and not *adding*?
How did you even get it to *count* to one?

Have you tried:

=COUNTIF(B2:C16,“Eligibility Supervisor”)

To actually perform a count?

Now ... if you really mean "SUMIF",
Where you really want numbers added, that correspond to the rows containing
“Eligibility Supervisor”, you will need an array where the rows and columns
of text would match the rows and columns of numbers.

For example, your array, B2:C16 that contains text, must match a similar
sized array of numbers ... say F2:G16.
Then, this formula would add the numbers that correspond to the text:

=SUMIF(B2:C16,“Eligibility Supervisor",F2:G16)

If I've guessed wrong as to what you need, post back with a more descriptive
question, since *most* folks around here don't usually open attatchments.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



Hi,

The text “Eligibility Supervisor” occurs in both Columns B and C within
the range B2:C16, yet the SUMIF in cell B25 only counts one occurrence
– the one in cell B16 I presume.

If I change the range to B2:B16 or C2:C16 it will count either 1 or 35
respectively (only those occurrences in that specific column).

TIA for your help.


File attached

Attachment filename: medi-cal training analysis-demo.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=585186

 

[Debra Dalgleish]

You can add the results of two SUMIF formulas. For example:

=SUMIF(B2:B16, A25, Staff_Counts)+SUMIF(C2:C16, A25, Staff_Counts)

 

[lindasf]

Debra,

Your advice worked perfectly - excpet that I changed it to:

=SUMIF(B2:B16,"Eligibilit
Supervisor",Staff_Counts)+SUMIF(C2:C16,"Eligibilit
Supervisor",Staff_Counts)

MS-EXCEL HELP indicates that one SUMIF statement should work for
range (e.g I wouldn't need to use 2) - but it's not the first tim
EXCEL has been buggy!

Thx. again