Sumif

[PGD15]

Hi,

Got a problem,

I want to use a SUMiF....

though i want to say if cell G1(tab1) = the cell in ranges A1:a350 (tab2) then sum the four cells to the right of A:A (tab2)
e.g. B1:e1 (if first row, or what ever row where this is true) if this is false = ""

 

[RoryA]

I think you mean:

=SUMPRODUCT((tab2!A1:A350=tab1!G1)*(tab2!B1:E350))
?

 

[PGD15]

I think you mean:

=SUMPRODUCT((tab2!A1:A350=tab1!G1)*(tab2!B1:E350))
?

I did, i just noticed and got it to work, keep forgetting about these danggg sum products

 

[PGD15]

got another question sorry, im trying to sum range D1:D100 (tab2) only if the value in range B1:B100 (tab2) is less than that in G1(tab1) BUT must be in the same year

The values in G1 and D1 are dates and in the same format.

I've tried
=sumif(tab2d1:d100,tab1Test(g1,"YY")<,text(tab2b1:b100,"YY")

but the text bit at the end and <> sign doesn't work with the text value of the date.

I tried with a sumifs too but couldn't figure it out

 

[RoryA]

If G1 is a date, and B1:B100 are also dates, you should just be able to use a < criterion since a date includes a year part.

 

[PGD15]

Sorry Probably wasn't clear the reason I cant use the <> criterion is that I only want the values less than the value in G1 provided they are from the same year, as using < g1 sums the value from the previous years too (which I don't want)

 

[RoryA]

Simplest way would be to add the first of that year as another criterion:

=sumifS(tab2!d1:d100,tab2!b1:b100,"<"&G1,tab2!b1:b100,">="&date(year(G1),1,1))

 

[PGD15]

Simplest way would be to add the first of that year as another criterion:

=sumifS(tab2!d1:d100,tab2!b1:b100,"<"&G1,tab2!b1:b100,">="&date(year(G1),1,1))

Ah didn't think to use the & ugh so obvious now i see
cheers!