Excel 2007 -->> SUMIFS v SUMPRODUCT

M

MurrayBarn

I have had some very useful help from Jacob on an older version of Excel
where he sorted my problem out with SUMPRODUCT (see Problem with Syntax?
posted below):

=SUMPRODUCT(--(TEXT(B2:B20000,"mmyyyy")="012008"),--(C2:C20000="Motor"),(D2:D20000+E2:E20000))

The above formula works great, but I have two questions for Excel 2007 users:
1. If I convert B2:B20000 to B:B, why do I get a #VALUE error
2. Will SUMIFS do the above and if so, how?

Thanks
 
E

Eduardo

Hi,
you have to change C , D and E as well, all the ranges in the formula has to
be the same

=SUMPRODUCT(--(TEXT(B:B,"mmyyyy")="012008"),--(C:C="Motor"),(D:D+E:E))
 
M

MurrayBarn

I tried that already but I get #VALUE error. I dont mind using 20000 but it
is a bit inelegant

Also, will SUMIFS work?
 
E

Eduardo

Hi,
That make not sense, formula is OK, please check if in D or E you dont have
any N/A or other error value, as well check that column B is format as text
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top