Count columns using just a formula
Hi there!
I've got an Excel book containing only on worksheet. In that sheet I've arranged the results of our servers' backup using one colum for each server in our company, and one row for each day of the year. I've attached a dummy version.
Every cell contains the result of each server backup for that day: Failed or OK.
I want to use a formula (I mean, not with VBA) to count how many servers failed to backup, that is, how many columns have some cell with content = "Failed".
I could do that by creating a new last row (the checking row) in which each cell checks if its colum has some "Failed" values, and then summing those cells to add up how many columns has failed. However, with this method, if I'd insert a new colum (i.e., add a new server) I'd have to extend the formula to the new cell in the checking row.
What I really want is some kind of smart formula (array formula or alike) that will adapt to inserting/deleting new columns (i.e., adding new servers or removing old ones) without having to change anything in the book.
Is that possible without using VBA?
Thank you.
Here's one possible solution.....
=SUM((MMULT(TRANSPOSE((B2:D11="Failed")+0),ROW(B2:D11)^0)>0)+0)
confirmed with CTRL+SHIFT+ENTER
not necessarily. Attached see a worksheet where the formula extends on column beyond the last server column. As a visual reminder, you see a gray column. As long as you insert new servers before the gray column, you won't have to update the formula that calculates the total number of servers that have failed (B14).However, with this method, if I'd insert a new colum (i.e., add a new server) I'd have to extend the formula to the new cell in the checking row.
Same principle is applied to the current row 13. If you insert new rows above row 12, the formulas will adjust the range automatically, because the formulas extend into the grayed row.
This way you can avoid complicated array formulae.
teylyn
Microsoft MVP - Excel
At Excelforum, you can say "Thank you!" by clicking theicon below the post.
Avoid pie charts with more than two data points. Why? See here (pdf, 559 kb). The only acceptable pie chart is here.
WOW!
That's exactly what I wanted daddylonglegs! I don't understand how it works, but it does. However, I'm not giving up! I'll discover how that crazy formula does its magic, no matter how long it takes me.
Thank you very much!
teylyn, thanks a lot anyway. I really appreciate your time and effort too, but daddylonglegs' solution is closer to what I was looking for.
I would say you could probably extend dll's formula by means of a Dynamic Named Range - ie instead of using a hard reference create a name as below
Assumes Server Names are text strings and that dates are numerical -- no numbers are entered below last date in column A.Code:Name: _ServerData RefersTo: =INDEX($1:$65536,2,2):INDEX($1:$65536,MATCH(9.99999999999999E+307,$A:$A),MATCH(REPT("Z",255),$1:$1))
As you add dates, insert columns etc the DNR will adapt accordingly and you should on that basis find you can use the below without ever needing to adjust for new ranges etc...
(and whilst only ever looking at the necessary cells)
As I say, just a suggestion...Code:=SUM((MMULT(TRANSPOSE((_ServerData="Failed")+0),ROW(_ServerData)^0)>0)+0) confirmed with CTRL + SHIFT + ENTER
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks