Is it possible to show a total (Countif, I think) for three differnet things in one cell?

[jaedmar]

I have multiple worksheets (range created and named "sheetlist"), each with a cell B15 for data. B15 has a drop down list and can be one of three things- "F", "E", or "L".

In cell B15 of my summary page, I want to list the total of each value. For example, if within the sheetlist there were a total of 3 "F"s, 2 "E"s, and 1 "L", I want cell B15 in the summary page to display "3, 2, 1".

Is that possible or would each have to be in it's own cell?

 

[Marcelo Branco]

Hi,

You need COUNTIF in multiple worksheets what in principle is not possible.

I found this by Aladin Akyurek

--------------------

If you just want to sum B1 across Sheet1 to Sheet3...

=SUM(Sheet1:Sheet3!B1)

If you just want to count numbers B1 houses across Sheet1 to Sheet3...

=COUNT(Sheet1:Sheet3!B1)

If you want to sum B1 when B1 > 0 (exluding negative values) across Sheet1
to Sheet3...

[1] If you have the morefunc.xll add-in installed:

For Conditional Summing:

=SUMPRODUCT(--(THREED(Sheet1:Sheet3!B1)>0),THREED(Sheet1:Sheet3!B1))

For Conditional Counting:

=COUNTIF.3D(Sheet1:Sheet3!B1,">0")

[2] If you need to do it without such an add-in:

For Conditional Summing:

=SUMPRODUCT(SUMIF(INDIRECT("'"&Sheets&"'!B1"),">0"))

For Conditional Counting:

=SUMPRODUCT(COUNTIF(INDIRECT("'"&Sheets&"'!B1"),">0"))

where Sheets refers to a range housing the relevant sheet names, that is,
Sheet1, Sheet2, and Sheet3.

--------------------------

HTH

M.

 

[AlphaFrog]

Try something like this...

=SUMPRODUCT(COUNTIF(INDIRECT("'"&sheetlist&"'!B15"),"F")) & ", " &
 SUMPRODUCT(COUNTIF(INDIRECT("'"&sheetlist&"'!B15"),"E")) & ", " &
 SUMPRODUCT(COUNTIF(INDIRECT("'"&sheetlist&"'!B15"),"L"))

 

[jaedmar]

That worked. Thanks.