Excel 2007 - Counting of blank cells with specific criteria

[nishu.raghavendra]

Hi Excel Forum,

I am new to this forum and looking for some help on the following:-

This is my current formula for calcuating the number of failed assets for a specific country

=SUMPRODUCT(ISNUMBER(FIND("GB_cz",QSC_Failed!$D$2:$D$2249))*ISNUMBER(FIND("UNIX",QSC_Failed!E$2:$E$2249))*ISNUMBER(FIND("105053",QSC_Failed!$G$2:$G$2249)))+
SUMPRODUCT(ISNUMBER(FIND("GB_cz",QSC_Failed!$D$2:$D$2249))*ISNUMBER(FIND("Linux",QSC_Failed!E$2:$E$2249))*ISNUMBER(FIND("105053",QSC_Failed!$G$2:$G$2249)))+
SUMPRODUCT(ISNUMBER(FIND("GB_cz",QSC_Failed!$D$2:$D$2249))*ISNUMBER(FIND("Solaris",QSC_Failed!E$2:$E$2249))*ISNUMBER(FIND("105053",QSC_Failed!$G$2:$G$2249)))+
SUMPRODUCT(ISNUMBER(FIND("GB_cz",QSC_Failed!$D$2:$D$2249))*ISNUMBER(FIND("AIX",QSC_Failed!E$2:$E$2249))*ISNUMBER(FIND("105053",QSC_Failed!$G$2:$G$2249)))+
SUMPRODUCT(ISNUMBER(FIND("GB_cz",QSC_Failed!$D$2:$D$2249))*ISNUMBER(FIND("FreeBSD",QSC_Failed!E$2:$E$2249))*ISNUMBER(FIND("105053",QSC_Failed!$G$2:$G$2249)))+
SUMPRODUCT(ISNUMBER(FIND("GB_cz",QSC_Failed!$D$2:$D$2249))*ISNUMBER(FIND("HP-UX",QSC_Failed!E$2:$E$2249))*ISNUMBER(FIND("105053",QSC_Failed!$G$2:$G$2249)))

Now to this I want to add another string, which can calculate the blank cells under the range $E$2:$E$2249 for the Czech Republic country under Column D with the code 105053 under the column G.

I have tried COUNTBLANK & COUNTIF array but they dont give me the correct number, would appreciate if one of you can help me with it.

Thanks & Regards
Raghu

[FDibbins]

Hi nishu and welcome to the forum

without seeing a sample workbook of your data etc, its hard to offer suggestions.

However, I would suggest hat you take a look at the sumifS() and countifS() functions that are new with 2007

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[nishu.raghavendra]

Hello Mr Dibbins, thank you for the help/suggestions provided. Here I am attaching a sample data, please let me know if this would help. I have removed some columns to protect the integrity of the data. Hope you understand.

Regards
Nishu