Count, but only in columns with specific value

[hcyouth]

sample.xlsxHi,

I've been using Excel lately to do some analysis on a table with numerical data for the different days of the week. I've been stuck on the following problem for a while now, so hopefully someone here has a quick solution:

I want to count the occurence of a certain value (using COUNTIF?), but only in columns that have a specific value on the first row. Which columns to include varies for each worksheet, so I want Excel to find them for me automatically. In other words: the range in my COUNTIF is a selection of columns, based on a specific value.

In pseudocode:

for each column in worksheet
if column contains value 'friday' on row 1
count number of occurences of value '-1' in this column

The total would be all occurences of the value -1 in the columns that contain the value 'friday' on row 1. The occurences of -1 on rows with 'saturday' - 'thursday' should NOT be counted.

Anyone with a quick and fast solution?

[mrice]

Maybe a formula like

=IF(A1 = "friday",COUNTIF(A:A,-1),"")

??

[dilipandey]

Hi hcyouth,

Welcome to the forum.

A1:G1 is the first row with some "friday"

A2:G5 have the data with some -1 value

Use the below formula:-

{=SUM(($A$1:$G$1="Friday")*($A$2:$G$5=-1))}

Above is an array formula and need to be entered using ctrl + shift + enter key combination. thanks.


Regards,
DILIPandey

<click on below 'star' if this helps>

[Fotis1991]

Hi and welcome to the forum

Lokks that you need COUNTIFS function, but we need more informations and even better, pls, upload a small sample workbook.

[hcyouth]

Thanks so far!

I see I haven't made my problem clear enough. I've uploaded a sample workbook to the original post. It contains data for two weeks. The problem: I want to count ALL values of '-1' but ONLY in columns that have 'friday' on the first row. Furthermore: I don't want to manually select the columns that have 'friday' on the first row, because this differs from worksheet to worksheet. I want to tell Excel that my range is all columns in the sample and then have it automatically select ONLY the columns that have 'friday' on row 1 and count all values of '-1' in those columns.

[hiteshasrani43]

Hi Hcyouth,

DILIPANDEY's result works for your worksheet.

Just edit a cell(double click) and enter the following code
=SUM(($A$1:$N$1="Friday")*($A$2:$N$5=-1)) and after this press "ctrl+shift+enter".

Wollah it works very well..

you'll see that the count shows "4"


Thank a ton DILIPANDEY

[hcyouth]

Thanks dilipandey / hiteshasrani43! It seems to work fine!

Offtopic: any pointers to books / websites on this topic? Just to give me a better background in what I'm doing (instead of just copying the result...)

[hiteshasrani43]

Hi,

I am very new to the forum, I think DILIPANDEY should have an answer to that, we will have to wait for his response

[Fotis1991]

Another option is using SUMPRODUCT function.

=SUMPRODUCT((A1:N1="Friday")*(A2:N7=-1))

See Bob's site for this..

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

[hcyouth]

I tried both solutions, both work fine!

However...both solutions regard empty cells as having a value of 0. So when I try to count the real value 0, it also counts all empty cells in a column...any solutions to that?

[Fotis1991]

Is this, works for you?

=SUMPRODUCT((A1:N1="Friday")*(A2:N7<>"")*(A2:N7=0))

[dilipandey]

Thanks dilipandey / hiteshasrani43! It seems to work fine!

@ hcyouth, you are welcome.. !!

Request you to mark this thread as [SOLVED] ...

Thank a ton DILIPANDEY

@ hiteshasrani43, you are welcome.. thanks for the appreciation

Regarding any helping material, search out Internet or may be Utube videos for this.. thanks.



Regards,
DILIPandey

<click on below 'star' if this helps>