count and sum based on criteria in multiple columns

[BBCline]

Capture.PNG

Hi guys,

I'm a newbie here and would appreciate your help. I've tried several different sumif, countifs, sumproduct expressions and still looking for a solution.

Does anyone know how to get the sum and count of the values in the questions cells (columns 2-4 in the attached file) based on the type of pet in the first column.
I used this

Please Login or Register  to view this content.

and it looks to be working but I don't know what to do to get the count equivalent of that.

I don't want to count what is in the first column, just use it as criteria to count what is in the other four columns. One last thing, can I also have an example that excludes counting or summing any values other than 1 to 5 in the four question columns.

I tried this code for the count but didn't work either

Please Login or Register  to view this content.

Thanks for any help given.

BB

[MarvinP]

Hi BB,

This is a very normal problem. You have your data as a Cross Tab table and it needs to be formatted differently to do much with it. See sheet 2 where I've moved the data to a better "Table" format and done a Pivot to get your answer.

[Moo the Dog]

And here is a solution using your data as formatted:

I2: =SUMIF(B2:E13,">0")
I3 and copied down: =SUMPRODUCT(($A$2:$A$13=$H3)*($B$2:$E$13>0)*($B$2:$E$13))

J2: =COUNTIF(B2:E13,">0")
J3 and copied down: =SUMPRODUCT(($A$2:$A$13=$H3)*($B$2:$E$13>0))

K2: =AVERAGE(B2:E13)
K3 and filled down: =AVERAGE(IF($A$2:$A$13=$H3,$B$2:$E$13)) <-- entered as array formula (Ctrl + Shift + Enter)

- Moo

[BBCline]

Hi Marvin,

thanks for the fast response. Unfortunately, the data has to stay in that cross tab format, I will continually update the first five columns with additional rows of data and just have the counts, sums and means updated.

I considered pivot tables but that would require manipulation of the rows and columns as you have in sheet two and that would need to be done every time I have new data to add. Did my first line of code make sense? It gave me the correct sum, except I would like to modify it to not include the negative values in the sum.

Thanks.

BB

[Moo the Dog]

See if my formulas above will help. Let me know if you have any questions/issues

- Moo

[BBCline]

Moo,

thanks, looks like your solution worked. can i add a couple new wrinkles, how do I edit your code to account for blank cells or ones that have negative values? They should not be included in the sum or count. When I deleted one of the values in the question columns, the sum decreased but the count did not.

thanks again

BB

[Moo the Dog]

Sorry for the delay... life gets in the way sometimes.

See my attached sheet for updated formulas, with the SUM and COUNT columns excluding blank and negative cells.

They also update properly when cells are deleted.

One question though... in the average column, the negative numbers are included. If you don't want that, let me know.

- Moo

[BBCline]

Moo,

not late at all, thanks for the new solution. I'll test tonight and report back.

BB

[BBCline]

Moo,

this is working, thanks a lot.

BB

[Moo the Dog]

You're welcome, BBCline. Glad to help, and thanks for the rep. It's always appreciated.

- Moo