PDA

View Full Version : EXCEL SUNIF using cell content as criteria



GrahamB
19-07-2004, 12:42 AM
I need to create a SUMIF that adds the values in Column C (Say range C1:C100), conditional upon the dates in Column A being greater than a date shown in Cell B101.

I come unstuck as soon as I try referencing the SUNIF condition to a cell value (B101). I have tried creating a formula using concatenation, but it comes out as text and I cannot convert it back to a formula.

I would appreciate assistance from someone out there!

TFYH

Regards
Graham

andrew93
19-07-2004, 01:04 AM
Hello Graham

I have struck this issue myself and unfortunately it is a limitation with the SUMIF function. You cannot use a cell reference in the second field of this formula. If I hard key a date (say May 15th) then the formula works, like this :

=SUMIF(A5:A77,">15/05/04",C5:C77) - assuming the values go from rows 5 to 77

It is important to use the same date formats. But I presume this is not what you want. A way around this problem is to use another column (say column B) to test the date and then use column B in the SUMIF function.

The formula in column B (assuming A5 to A77 has the dates, C5 to C77 has the values and B101 has the reference date) would look like this :

=IF(A5>$B$101,1,0)

and the SUMIF formula would look like this :

=SUMIF(B5:B77,"=1",C5:C77)

Not pretty but it works.

I'd be interested in seeing the concatenation attempt - do you want to post your formula and I will have a look?

HTH
Andrew

rugila
19-07-2004, 09:06 AM
Hi GrahamB and andrew93

Enter in the cell that you want the sum to appear (say C101) the following formula:
=SUM(IF(A1:A100>$B$101,C1:C100))

Then, don’t click the tick (or press enter) but instead press CTRL-SHIFT-CLICKTICK, (or CTRL-SHIFT-ENTER.)

If you don’t press the CTRL-SHIFT you just get a #VALUE!, if you do you should get the right answer.

An example of an array formula.

You can probably do it without arrays using the SUBSTITUTE function and conditional sums, but that line gets a bit messy to be worthwhile in my view.

parry
19-07-2004, 09:44 AM
Hi Graham, further to Andrews comments regarding SUMIF, you can actually use a cell reference. The second argument for the function requires this to be text - hence the quotes around the expression - so as you have found out you cant just put the cell reference.

To get around this use a combination of text for your operator (>, < , <>,=) then join this (using &) with the TEXT function. This function converts the value of a cell into text so this is what you need for SUMIF to work.

Heres an example ...
=SUMIF(A1:A100,">" & TEXT(B101,"DD/MM/YY"),C1:C100)

GrahamB
19-07-2004, 01:28 PM
Thanks for the various inputs.

Andrew yours didn't allow me to repaeat the process continuially because for each line I would have need a separate Column B .

Rugila, I need to learn more about Control/Shift formulae, but it would not have workrd in my case because I nee the calculation to be continuous down a page.

Parry, yours worked great! I found that the cell tended to default to a DATE format or a CUSTOM format depending on the spaces between the ">" & TEXT( part. But whichever was, if I reset the cell format to GENERAL it works.

Thank you all muchly for your input!

Regards
Graham Bockett

rugila
20-07-2004, 01:24 AM
Hi Graham Bockett,

I’ve never posted an Excel (or other) formula on this, or any other forum, that doesn’t work.

I carefully checked the one I supplied to ensure it worked in the situation you specified, particularly over your trouble with cell references. It did everything you specified, and did so correctly.

You say:
>but it would not have workrd in my case because I nee the calculation to be continuous down a page

It’s not clear just what you meant by this, since that formula can give continuous calculations as much down a page as the user likes. Are you able to expand a bit on why “it would not have workrd”?

It’s not useful for me to spend time (although not a lot in this case) to help out with a problem, only to be told the solution doesn’t work, when in fact it did everything that was requested.

I don’t mind you not using that formula, but to accuse it of “it would not have workrd”, without apparently trying to ascertain whether it would work or not, I do mind, as that is totally unmotivational towards helping with problems on which you specifically requested help. (As you said, “I would appreciate assistance from someone out there!”)

GrahamB
20-07-2004, 09:29 PM
Hi Ruglia

Thanks for your comments.

Perhaps I was too quick to judge its effectiveness BECAUSE I have not uses the <CNTRL><SHIFT> process before. As I understand it, that would require the <CNTRL><SHIFT> action each time you wanted a recalculation in any cell. If I am wrong in that then please accept my apologies. Please be assured I was not denying the effectiveness of your offering.

Despite the fact that I have got my particular problem sorted with one of the other formulae offered, I will have a go with yours and see if I can get it to work in the multirowed environment I am using.

Regards
Graham

parry
21-07-2004, 01:26 AM
Hi Graham, Ruglias formula works fine. The only problem with array formulas is that a large number of Excel users find them a mystery and have never used them or dont understand them properly. Have a look in Excel Help under the topic 'About array formulas and array constants'. They can be very powerful to determine solutions to problems that would otherwise require a mamoth formula or several formulas.

When you enter an array formula you must press CTRL+SHIFT+ENTER keys all at once. You will know you have done it correctly if the formula you typed in now has braces around it like this {=SUM(IF(A1:A100>$B$101,C1:C100))}

Note that typing in the braces yourself is not the way to do it and it wont work correctly if done that way. They operate just like other formulas so if any of the target cells change then the formula will update etc. You can drag the formula down to other cells as well & you dont have to press CTRL+SHIFT+ENTER when you do this - only when entering the formula itself.

A thing you have to be aware of is that if you need to amend the formula then remember to use CTRL+SHIFT+ENTER or it will revert to a standard formula and give you an incorrect result. You would also need to educate other users of your book as well.

hth

GrahamB
22-07-2004, 10:33 AM
Thanks Parry

That was very informative, and obviously I will have to put my head into Array Formulae

Thanks
Graham