SUMIF function

[Shengwen Zhang]

Hi,

I need to sum the numbers (in B column) that their
corresponding cells (in A column) are smaller than a value
in a designated cell.

I know this works:
D172=SUMIF(A:A,"<23",B:B)

But how do I do this?
D172=SUMIF(M:M,<C172,N:N)
where C172 corresponds D172, so I can copy the function.

Thanks.

 

[JE McGimpsey]

One way:

D172: =SUMIF(M:M,"<" & C172,N:N)

 

[Guest]

Thanks a bunch. Such formular is not suggested by the
Excel help file. Where can I get a comprehensive manual?
Or people just do try-and-error with some knowledge in
languages?

Regards,

Shengwen

 

[JE McGimpsey]

I'm surprised it isn't covered in Help, too. I've made a suggestion to
MS that they revise that help topic. You should, too - send an email to

(e-mail address removed)

Make sure you include "Excel" in the subject line so that the message is
routed to the proper program manager(s).

 

[hgrove]

Anonymous wrote...

Thanks a bunch. Such formular is not suggested by the Excel
help file. Where can I get a comprehensive manual? Or people
just do try-and-error with some knowledge in languages?

...

Excel's manuals have always left something to be desired, such a
detailed rather than superficial examples. There are a number of decen
Excel books (according to others), but nothing of note from Microsoft.

As for figuring out formuas, it doesn't have to be trial and error
Basic engineering principles apply to formula creation. In the case o
SUMIF, online help is clear enough that its second argument must be
text-valued expression evaluating to a valid criteria expression. I
"<20" is a valid criteria, and if "<"&20 evaluates to "<20", and i
cell X99 evaluates to 20, then it shouldn't come as a complete surpris
that "<"&X99 would be equivalent to "<20".

At some point manuals should be able to dispense with having to explai
that going from point A to point C involves passing through point B
This comes much more easily to those with a programming background
Spreadsheet formulas are a kind of functional programming language, an
it's usually best to approach formula creation as a form of programming