Sumif & AND function

  • Thread starter Thread starter Lut
  • Start date Start date
L

Lut

I've read various posts, but haven't been able to solve this one:

Column A is a date field (can be earlier or later than Today)
Column B is a text field (ej. Yes or No)
Column C is a numeric field (ej. 1,2, 3 etc)
Column D is a numeric field as well

What I need to do is sum the values of column D which meet a specific
criteria for columns A, B, AND C. (More specifically, I need to add
up all values of column D for all dates earlier than today (column A),
"Yes" in column B, 2 in column C).

Of course I could do this with MsAcces or Msquery or with a pivot. The
problem here is that I need a single result in one cell in excel. I
can also try a work around adding columns to the data sheet itself
with AND functions and then selecting only TRUE or FALSE with an sumif
funcion, but this is not good either as the selection of the text
field (column B) is quite long and in fact linked to a cell reference
a not to a given. (In the abobe example column B would have to meet
the criteria of that of a cell in ej "sheet2")

Can somebody help...

Lut
 
One way

=SUMPRODUCT(--(A2:A30<TODAY()),--(B2:B30="Yes"),--(C2:C30=2),D2:D30)

--
For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom
 
If that does not work then the conditions that you stated are not true

--
For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom
 
Maybe you need to switch from comma to semi-colon...

=SUMPRODUCT(--(A2:A30<TODAY());--(B2:B30="Yes");--(C2:C30=2);D2:D30)
 
Hi
you may use the semicolon as separator. try:
=SUMPRODUCT(--(A1:A100<TODAY());--(B1:B100="Yes");--(C1:C100=2);D1:D100
)
 
Hi Peo
i think the OP uses the semicolon as separator (due to a mail he sent
me privately)
 
Are you using semicolon as delimiter as opposed to comma?

--
For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom
 
Thank you all for your help..
Frank resolved the problem...and for other users, this golden tip he gave:
Note: SUMPRODUCT does not accept ranges like A:A. always use something like
A1:A1000

good day to all
 
Back
Top