RE: sumif with 2 criteria?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance


This is what my formula looks like:

=sumproduct((Tabelle3!$B:$B=Tabelle1!$C6)*(Tabelle3!$C:$C=Tabelle1!E$5)*(Tabelle3!$D:$D))

"Tabelle1!$C6" is a 6 digit number which has been converted to a text format
"Tabelle1!E$5" is a word
"Tabelle3!$D:$D" this contains the turnover (currency)

So far, its still coming back with a number error...


"Mike H" wrote:

Hi,

In you example formula if you are searching for "a" then you must put it in
quotes, if you are searching for number then you don't use quotes.

so

=sumproduct((C18:C31="a")*(D18:D31="q")*(E18:E31))


or

=sumproduct((C18:C31=99)*(D18:D31=100)*(E18:E31))


or better still use cell references for the criteria

=sumproduct((C18:C31=A1)*(D18:D31=B1)*(E18:E31))


Mike

"Wombat" wrote:

Thanks for your answers. I think I get the principle of the formula but I get
a #NAME error when I try it...


My version of Excel (2003 in German!) seems to want semi-colons instead of *
symbols. Does this make a difference?

Advice?
.

Relevant Pages

  • Re: Trim and Substitute not working
    ... but as a number with no decimal points, format as number with no decimal ... digit to zero, you presumably started with more than 15 digits. ... I've tried substitute - it dint work. ... i want to remove the single quotes before the number. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Where does fmtutil get its inputs?
    ... Let's look at a real example; building the 'latex' format: ... uses the pdfetex engine. ... You can do the same searching along these ...
    (comp.text.tex)
  • Re: C# is ignored on 2000 Server in mssearch
    ... > Server 2003 langwrbk.dll wordbreaker, although I've not tested these single ... > Microsoft for specificly searching for these programming languages. ... > C without double quotes ... > David', ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Summation, modulo and decimal
    ... learn how to behave in usenet. ... The quotes can be most easily provided when you use google by ... thereby providing context. ... I told you exactly how to get the k-th digit. ...
    (sci.math)
  • RE: Export records. Header? Yes please, "Quotes"? No thanks.
    ... You might try using the merge format for your export. ... Yes please, "Quotes"? ... Perhaps FileMaker needs to consider adding "Strict CSV" as one of its ... Excel does eventually get to use the data file - but not directly. ...
    (comp.databases.filemaker)