Help with using Countif() across multiple columns

[rcarruth]

I am trying to find a cleaner way to get a count of several cells in different columns in which the value is greater than zero.
In the workbook I've attached at the bottom of the worksheet is "Avg Carb" and in the Count section I've used a bunch of Countif() to achieve the result, I am trying to see if there is cleaner code or an easier function that I could use to get the same results.
For further explanation, the cells that the Countif() is calling to is a sum of all the numbers for each "carb" column.
Let me know if there are any questions about what I am trying to do.
I appreciate any help with this.
Thanks
Bob

[sktneer]

Try this......

Please Login or Register  to view this content.

[windy58]

Did a search and found this...

=SUM(COUNTIF( INDIRECT({"C13","E13","G13","I13","K13","M13","O13","C27","E27","G27","I27","K27","M27","O27","C41","E41","G41","I41","K41","M41","O41","C55","E55","G55","I55","K55","M55","O55"}), ">0"))

Bit tidier.

Windy

[rcarruth]

Try this......

Please Login or Register  to view this content.

Worked great for that group but nothing changes when I put something in cell C18 or anywhere else outside of the range B3:0O3
Tried this code found by windy58:
=SUM(COUNTIF( INDIRECT({"C13","E13","G13","I13","K13","M13","O13","C27","E27","G27","I27","K27","M27","O27","C41","E41","G41","I41","K41","M41","O41","C55","E55","G55","I55","K55","M55","O55"}), ">0"))
and it worked, anyway to shorten this?

[rcarruth]

Did a search and found this...

=SUM(COUNTIF( INDIRECT({"C13","E13","G13","I13","K13","M13","O13","C27","E27","G27","I27","K27","M27","O27","C41","E41","G41","I41","K41","M41","O41","C55","E55","G55","I55","K55","M55","O55"}), ">0"))

Bit tidier.

Windy

That worked!!

[Saarang84]

More simpler approach :

In the Avg Carb table, try,

Please Login or Register  to view this content.

[Teethless mama]

That worked!!

......but it's not an elegant solution

Try this:

=SUM(COUNTIFS(B3:O3,"Carbs",INDIRECT({"B27:O27","B13:O13","B41:O41","B55:O55"}),">0"))

[rcarruth]

......but it's not an elegant solution

Try this:

=SUM(COUNTIFS(B3:O3,"Carbs",INDIRECT({"B27:O27","B13:O13","B41:O41","B55:O55"}),">0"))

I'm actually using this code which seems to be working so far:
=COUNTIFS(B13:O55,">0",B3:O45,"Carbs")

I am using this spreadsheet in Google Docs and I'm finding that some of the functions which are working perfectly on a desktop Excel sheet do not work the same on Google Docs, very frustrating.
Thanks for your help though.
Bob

[Teethless mama]

I'm actually using this code which seems to be working so far:
=COUNTIFS(B13:O55,">0",B3:O45,"Carbs")

I am using this spreadsheet in Google Docs and I'm finding that some of the functions which are working perfectly on a desktop Excel sheet do not work the same on Google Docs, very frustrating.
Thanks for your help though.
Bob

Excel <> Google Docs

I know not thing about google doc.

Google Docs may work on some basic formula, but I don't think it will work with complex formula.

[sktneer]

You may try this to see if this works on google spreadsheet

Please Login or Register  to view this content.

On Excel, the formula will be as follows.....

Please Login or Register  to view this content.

[Jacc]

Here is my take on it. I added a helper column (A) to indicate the data rows to be summed and averaged, makes the formulas more compact.
These are array formulas, they have to be entered with Ctrl + Shift + Enter.

[Tony Valko]

......but it's not an elegant solution

Try this:

=SUM(COUNTIFS(B3:O3,"Carbs",INDIRECT({"B27:O27","B13:O13","B41:O41","B55:O55"}),">0"))

......but it's not a robust solution

Try this:

=SUMPRODUCT((B3:O3="carbs")*(MOD(ROW(B13:O55)-ROW(B13),14)=0)*ISNUMBER(B13:O55)*(B13:O55>0))

[XOR LX]

......but it's not a robust solution

Try this:

=SUMPRODUCT((B3:O3="carbs")*(MOD(ROW(B13:O55)-ROW(B13),14)=0)*ISNUMBER(B13:O55)*(B13:O55>0))

Why not "robust"?

Regards

[Tony Valko]

=SUM(COUNTIFS(B3:O3,"Carbs",INDIRECT({"B27:O27","B13:O13","B41:O41","B55:O55"}),">0"))

If you happen to insert new rows/columns before the referenced ranges the formula "breaks".