MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Using SUMIF based on a condition


Posted by Mary Archer on February 10, 2002 10:20 AM

I have a row of numbers containing alternate exam raw scores and percentage scores;
e.g.
64 85% 38 72% 12 80% 55 95%

Can I use the SUMIF function to add just the percentages together ignoring the raw score numbers?


Posted by Aladin Akyurek on February 10, 2002 10:33 AM

Mary --

Just to make sure: Do you want to really sum the percentages or average them?

And, Could you give the exact range where you have these raw scores interspersed with percentages?

Aladin

=======

Posted by Neil on February 10, 2002 11:15 AM

Assuming your data is in A1 to A8 use =SUMIF(A1:A8,"<1",A1:A8). If you want averages divide the above by the COUNTIF(A1:A8,"<1")

Posted by Aladin Akyurek on February 10, 2002 11:34 AM

> Assuming your data is in A1 to A8 use =SUMIF(A1:A8,"<1",A1:A8). If you want averages divide the above by the COUNTIF(A1:A8,"<1")

In practice, it's a solution which is good enough.

What happens if one of the raw scores is 0 or 0.5?

========

Posted by Larry Kramer on February 10, 2002 2:05 PM


You can do this with an array formula. If your row of scores is called "scores", this formula will sum every other entry starting with the second one (i.e, the first percentage):

{=SUM(IF(MOD(COLUMN(scores),2)=0,scores,0))}

To enter an array formula, type this formula without the brackets and hit Ctrl-shft-Enter.

If you want the average, substitute Average for Sum.

(This solution assumes that the first raw score is in an odd-numbered column (e.g, the first column, column A, of the spreadsheet. If the first column is an odd column, substitute =1 for =0. Alternatively, name the first raw score "scores1" and use this formula:

{=SUM(IF(MOD(COLUMN(scores)-COLUMN(scores1)+1,2)=0,scores,0))}

Then you can put the scores anywhere you want and the formula will adjust.

Posted by Aladin Akyurek on February 10, 2002 2:39 PM

Summing or averaging percentage values in every other row

I should have proposed what I had in mind instead of questioning the OP where the data start and whether the percentages must be averaged or summed. By the way, I'd go for Neil's proposal if it's guaranteed that there will be no RAW scores below 1.

Otherwise, I'd suggest using an array formula or SUMPRODUCT formula using Boolean terms (no IFs):

If the first RAW score of the data start in an UNEVEN numbered row, use

either ordinarily entered

=SUMPRODUCT((MOD(ROW(A1:A8),2)=0)*(A1:A8))/MAX(1,(COUNT(A1:A8)/2))

or array-entered

{=SUM((MOD(ROW(A1:A8),2)=0)*(A1:A8))/MAX(1,(COUNT(A1:A8)/2))}

If the first RAW score of the data start in an EVEN numbered row, use

either ordinarily entered

=SUMPRODUCT((MOD(ROW(A2:A9),2)<>0)*(A2:A9))/(MAX(1,(COUNT(A2:A9)/2))

or array-entered

{=SUM((MOD(ROW(A2:A9),2)<>0)*(A2:A9))/(MAX(1,(COUNT(A2:A9)/2))}

If summing is the goal, drop the demonitor from the above formulas.

==========

Posted by mary archer on February 11, 2002 2:15 AM

Posted by mary archer on February 11, 2002 2:17 AM

Actually you are right - I do want to average them - that's why I need to convert raw scores to percentages. There will be multiple rows i.e. one for each student - can be anywhere on the spreadsheet - don't care!

Posted by mary archer on February 11, 2002 2:21 AM

Thanks Neil I think I tried this and it works fine except when a student has scored 100% and then it doesn't get counted!

Posted by Aladin Akyurek on February 11, 2002 2:37 AM

Mary --

That is another problem with this otherwise nice formula: it will see 100% as 1.0 which of course does not meet the condition "<1".

I'd suggest using one of the SUMPRODUCT formulas that I suggested.

Cheers.

Aladin

===========

Posted by Neil on February 11, 2002 10:57 AM

So you have some clever ones and some dumb ones!

Data in the same place, put =IF(ISEVEN(ROW(A2)),A2,"") in B2 and copy down as far as you need, then average (or whatever) column B. You can try ISODD as well for the raw data. > Assuming your data is in A1 to A8 use =SUMIF(A1:A8,"<1",A1:A8). If you want averages divide the above by the COUNTIF(A1:A8,"<1") In practice, it's a solution which is good enough. What happens if one of the raw scores is 0 or 0.5? =