Help with combining 2 formulas

[Guest]

I would like to combine the following 2 formulas into 1 formula, yet I can't
seem to find the correct way to do it, if it is possible.

=SUMIF(Invoices!$J:$J,"="&(TEXT(E1,"mmmmmmmmm-yyyy")),Invoices!F2:F2000)
=SUMIF(Invoices!A2:A2000,"="&(TEXT(A21,"00000000")),Invoices!F2:F2000)

In short I want this cell to check a list of invoices and display everything
by month and by account code. I would have to look at column A for the
account code and column J for the date. In the master sheet I want an
overview per month per account.

I would appreciate any help.


I would appreciate any help.

 

[Guest]

I think sumproduct would do what you want
=sumproduct(--(Invoices!$J:$J=(TEXT(E1,"mmmmmmmmm-yyyy")),--(Invoices!A2:A2000=(TEXT(A21,"00000000")),Invoices!F2:F2000)

 

[Guest]

I think you need SUMPRODUCT:

=SUMPRODUCT(--(Invoices!$J2:$J2000=TEXT(E1,"mmm-yyyy")),--(Invoices!A2:A2000=TEXT(A21,"00000000")),Invoices!F2:F2000)

HTH

 

[Dave Peterson]

Maybe...

If you're using xl2007, look at =countifs() in excel's help.

This will work in all versions:

=sumproduct(--(invoices!j2:j2000=text(e1,"mmmm-yyyy")),
--(invoices!a2:a2000=text(a21,"00000000")),
invoices!F2:f2000)

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

=======
ps. I'd use:
=SUMIF(Invoices!$J2:$J2000,TEXT(E1,"mmmm-yyyy"),Invoices!F2:F2000)
=SUMIF(Invoices!A2:A2000,TEXT(A21,"00000000"),Invoices!F2:F2000)
in your original formulas

 

[Guest]

Ok thanks so far, at least it stops erroring now. Now to get a number instead
of zero. The sum is not zero, I will try and tweek it.

Thanks so much so far !

 

[Dave Peterson]

mmm or mmmm in that =text() portion?

But if the criteria don't match--Invoices!J2:J2000 has to be text--not a real
date.

And invoices!A2:A2000 has to be text, too.