I have a spreadsheet that has 13 columns the first column for an identifier then 12 more columns one for each month. I can add a sum column but my data is based on a query and the number of rows changes constantly. Currently I am using sumif(A:A,"criteria",B:B)+sumif(A:A,"criteria",:C: C)+and so on so that I get all twelve months of data. Is there a better way to do a sumif that sums up all columns of data as opposed to adding 12 sumifs?

Thanks

Hi Dave,

Am Wed, 6 Jul 2016 22:45:37 +0100 schrieb Dave H:

I have a spreadsheet that has 13 columns the first column for an
identifier then 12 more columns one for each month. I can add a sum
column but my data is based on a query and the number of rows changes
constantly. Currently I am using
sumif(A:A,"criteria",B:B)+sumif(A:A,"criteria",:C: C)+and so on so that I
get all twelve months of data. Is there a better way to do a sumif that
sums up all columns of data as opposed to adding 12 sumifs?

try:
=SUMPRODUCT((A2:A100=criteria)*B2:M100)


Regards
Claus B.
--
Windows10
Office 2016

Thanks that works!

Also,

you can use arrary formula (sum with if)

sum(if((col1="a")*(col2="b"),sum_range,0))

enter with cntl+shift+entter

On Thursday, 7 July 2016 03:19:06 UTC+5:30, Dave H wrote:
I have a spreadsheet that has 13 columns the first column for an
identifier then 12 more columns one for each month. I can add a sum
column but my data is based on a query and the number of rows changes
constantly. Currently I am using
sumif(A:A,"criteria",B:B)+sumif(A:A,"criteria",:C: C)+and so on so that I
get all twelve months of data. Is there a better way to do a sumif that
sums up all columns of data as opposed to adding 12 sumifs?

Thanks




--
Dave H