i've been using this formula for calculating a total in a column. I need to modify it to calculate the total of TWO columns
=COUNT(TRANSPOSE(INDIRECT(ADDRESS(2+3*ROW(INDIRECT ("1:75")),3))))+COUNT(TRANSPOSE(INDIRECT(ADDRESS(3+3*ROW( INDIRECT("1:75")),3))))
thanks.
...anyone?
...hello?...
If the second column is next to the one being referenced in your formula, for example Column D...Originally Posted by ForSale
=SUM(IF((MOD(ROW(C5:C228)-ROW(C5),3)<>2),--ISNUMBER(C5:D228)))
...confirmed with CONTROL+SHIFT+ENTER.
If the second column is not next to the one being referenced in your formula, for example Column F...
=SUM(IF((MOD(ROW(C5:C228)-ROW(C5),3)<>2),ISNUMBER(C5:C228)+ISNUMBER(F5:F228)))
...confirmed with CONTROL+SHIFT+ENTER.
Hope this helps!
Thanks a lot Domenic!
Can you explain a little of what this formula does, so that i can modify it to work in other columns?
for instance, if i want to put this at the end of column n does it need to say
=SUM(IF((MOD(ROW(n5:n228)-ROW(n5),3)<>2),--ISNUMBER(n5:n228)))
?
Thanks
Yes, that's exactly it.
Basically, here's how the formula works...
ROW(N5)-ROW(N5), equals 0.
The MOD function then uses this result for it's first arguement...
MOD(0,3), which equals 0.
The IF function then uses this as part of its first arguement...
IF(0<>2, which equals TRUE.
Since the result is TRUE, the second arguement of the IF function is evaluated...
--ISNUMBER(N5), which returns 1 if the cell contains a number and 0 if it contains something other than a number or the cell is empty.
Here, ISNUMBER returns TRUE or FALSE, and the 'double minus' sign coerces TRUE/FALSE to their numerical equivalents of 1/0, respectively.
If, however, the evaluation from the first arguement in the IF function is FALSE, then FALSE is returned.
Then this whole process is repeated with the next cell in the range, that being N6. After every cell in the range has been evaluated, the SUM function then adds up all of the values returned.
Hope this helps!
Last edited by Domenic; 01-12-2005 at 10:54 AM.
Thanks again Domenic, sorry if i seemed pushy or anything.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks