SUMIF times 3

  • Thread starter Thread starter Ant
  • Start date Start date
A

Ant

Hello every one.

=SUM(IF(($AK$2:$AK10000,AV3)*($AO$2:$AO10000,AV4)*
($AM$2:$AM10000=AV5),$U$2:$U499))


I need a function that works like a pivot table would.

I have a huge worksheet with over 10000 entrees in it

Now I need the function to look at three cells, based on
what the user types I need the function to add a few
columns

In cell A1, the user types in the year (2003) than the
function look through columns AL1: Ak10000 than adds
everything in range o2:o10000
This function works great

SUMIF($AK$2:$AK10000,A1,$O$2:$O10000)

However I also want the function to look at cell B5 and
see what month was type in

So the function would only add (for example) January 2003


And finally one more criteria,

Look in cell c5 for the account the user types in (for
example) "21210" so it will add only the 21210 account
for January 2003
Any ideas?
 
=SUMPRODUCT((RngA=xx)*(RngB=yy)*(RngC=zz)*(RngD))

will sum RngD if all criteria are met in Rngs A:C

xx, yy, zz can be cell references with the criteria in them or can be hardcoded
into the formula, but the former is usually preferable. If you hardcode a date
into the formula then use something like *(RngB=DATEVALUE("25/02/04"))* to
evaluate it.
 
I dont understand

what does RngA mean?
can you use my forumal which work already and modify tham?
 
=SUMPRODUCT((AK2:AK10000=AV3)*(AO2:AO10000=AV4)*
(AM2:AM10000=AV5)*(O2:O10000))

is not working at all, help
 
try this where the daterng is valid dates and you want the 1st month. or put
1 in b5 and use =b5 instead
=sumproduct(year(daterng)=2004)*(month(daterng)=1)*(acctrng=211330)*sumrng)
 
It will be this kind of format, but I'm not sure what you have in what columns,
so I may not have tied the right variable cells (ie A1,B5,C5) to the correct
columns.

=SUMPRODUCT(($AK$2:$AK10000=A1)*($AO$2:$AO10000=B5)*($AM$2:$AM10000=C5)*($U$2:$U
1000))

Note though, ALL ranges MUST be the same size, and NOT include the headers.
 
SUMPRODUCT(YEAR(AO2:AO10000)=AV4)*(MONTH(AM2:AM10000)=AV5)*
(AK2:AK10000=AV3)*O2:O10000

is not working y?
 
SUMPRODUCT(($AK$2:$AK10000=AV3)*($AO$2:$AO10000=AV4)*
($AM$2:$AM10000=AV5)*($O$2:$O10000))

Returs NA#
 
SUMPRODUCT(YEAR(AO2:AO10000)=AV4)*(MONTH(AM2:AM10000)=AV5)*
(AK2:AK10000=AV3)*O2:O10000
Click to expand...
add another ( at the front and one at the end
SUMPRODUCT((YEAR(AO2:AO10000)=AV4)*(MONTH(AM2:AM10000)=AV5)*
(AK2:AK10000=AV3)*O2:O10000)
 
Hi
are there text values in column O. If yes try
SUMPRODUCT(($AK$2:$AK10000=AV3)*($AO$2:$AO10000=AV4)*
($AM$2:$AM10000=AV5),$O$2:$O10000)
 
Back
Top