I have done it before that the sum area can contain text and the formula is able to ignore those (such as where the header lies. I just can't remember how I did it.
Thanks,
Jp
0
Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.
I am trying to prevent an error in the event another row gets added above row4. Could the sumproduct be used for the entire column instead, utilizing my example?
Sorry, ignore previous reply, I misunderstood the query. Jppinto's solution is good....but if you want to allow text in the sum range you need to switch to the "native" syntax of SUMPRODUCT with a comma instead of multiplying the sum range with *, i.e.
=SUMPRODUCT((A4:A7=F3)*(B4:B7=F4),C4:C7)
You can't use the whole column in Excel 2003 (you can in Excel 2007) but you can use all but one cell like
>I belive that JP requested "Could the sumproduct be used for the entire column instead"... that's why I presented that formula...
...but that formula won't work in 2003 - you can't use whole columns in any array formula in Excel 2003 - and if you have 2007 or later you can simply use
=SUMPRODUCT((A4:A7=F3)*(B4
jppinto