SUMIF curly one!

  • Thread starter Thread starter louncov
  • Start date Start date
L

louncov

G'day,

Am using the following formula to summarise costs between 2 dates:

=SUMIF(A3:A500,">"&DATE(2003,6,30),G3:G500) -
SUMIF(A3:C500,">"&DATE(2003,7,31),G3:G500)

This extracts totals [in G3:G500] between the above date range. That's
fine, but I need to add another criteria to this. In column 'B', I use
a 'code' to determine what type of stock has been ordered/purchased.
The 'codes' are defined in another sheet (within the same workbook),
so I need the above formula modified to include reference to sheet
'Codes!B4. This way I can break down my different stock per date
range.

All help is appreciated.
Thanks, Cov.
 
Hi

For such tasks is SUMPRODUCT function better!
=SUMPRODUCT(A3:A500>DATE(2003,7,0)*(A3:A500)<DATE(2003,8,1)*(B3:B500=Codes!B
4)*(G3:G500))
(I corrected your date interval a bit - probably you wanted the last of
month included too. And I used DATE() in a way you don't need to know the
last of month at all)


Arvi Laanemets
 
louncov said:
G'day,

Am using the following formula to summarise costs between 2 dates:

=SUMIF(A3:A500,">"&DATE(2003,6,30),G3:G500) -
SUMIF(A3:C500,">"&DATE(2003,7,31),G3:G500)

This extracts totals [in G3:G500] between the above date range. That's
fine, but I need to add another criteria to this. In column 'B', I use
a 'code' to determine what type of stock has been ordered/purchased.
The 'codes' are defined in another sheet (within the same workbook),
so I need the above formula modified to include reference to sheet
'Codes!B4. This way I can break down my different stock per date
range.

All help is appreciated.
Thanks, Cov.
Click to expand...

I suggest you change to SUMPRODUCT. In general, this is of the form
=SUMPRODUCT( (criterion1) * (criterion2) * (criterion3) * data)
You can have one or more criteria.

Your existing formula can be re-written thus:
=SUMPRODUCT((A3:A500>DATE(2003,6,30))*(A3:A500<=DATE(2003,7,31))*G3:G500)
or
=SUMPRODUCT((MONTH(A3:A500)=7)*(YEAR(A3:A500)=2003)*G3:G500)

So, adding your new criterion, we get:
=SUMPRODUCT((A3:A500>DATE(2003,6,30))*(A3:A500<=DATE(2003,7,31))*(B3:B500=Co
des!B4)*G3:G500)
or this:
=SUMPRODUCT((MONTH(A3:A500)=7)*(YEAR(A3:A500)=2003)*(B3:B500=Codes!B4)*G3:G5
00)
 
Anon said:
louncov said:
G'day,

Am using the following formula to summarise costs between 2 dates:

=SUMIF(A3:A500,">"&DATE(2003,6,30),G3:G500) -
SUMIF(A3:C500,">"&DATE(2003,7,31),G3:G500)

This extracts totals [in G3:G500] between the above date range. That's
fine, but I need to add another criteria to this. In column 'B', I use
a 'code' to determine what type of stock has been ordered/purchased.
The 'codes' are defined in another sheet (within the same workbook),
so I need the above formula modified to include reference to sheet
'Codes!B4. This way I can break down my different stock per date
range.

All help is appreciated.
Thanks, Cov.
Click to expand...

I suggest you change to SUMPRODUCT. In general, this is of the form
=SUMPRODUCT( (criterion1) * (criterion2) * (criterion3) * data)
You can have one or more criteria.

Your existing formula can be re-written thus:
=SUMPRODUCT((A3:A500>DATE(2003,6,30))*(A3:A500<=DATE(2003,7,31))*G3:G500)
or
=SUMPRODUCT((MONTH(A3:A500)=7)*(YEAR(A3:A500)=2003)*G3:G500)

So, adding your new criterion, we get:
=SUMPRODUCT((A3:A500>DATE(2003,6,30))*(A3:A500<=DATE(2003,7,31))*(B3:B500=Co
des!B4)*G3:G500)
or this:
=SUMPRODUCT((MONTH(A3:A500)=7)*(YEAR(A3:A500)=2003)*(B3:B500=Codes!B4)*G3:G5
00)
Click to expand...

-------------
Thanks to all for your help & input. Further to "Anon's" post, I
removed the 'Codes' sheet reference and ended up with:

=SUMPRODUCT((MONTH(A3:A500)=7)*(YEAR(A3:A500)=2003)*(D3:D500="rm")*G3:G500)

....which worked fine. Great. OK, one more question...

The above gave me the cost of 'rm' over July. How can I get the COUNT
of 'rm' in July? Would I use the COUNT/COUNTIF with the same date
ratio?
 
Hi

Remove the last array (G3:G500) from your formula, or replace it with 1

Arvi Laanemets


louncov said:
"Anon" <none> wrote in message
louncov said:
G'day,

Am using the following formula to summarise costs between 2 dates:

=SUMIF(A3:A500,">"&DATE(2003,6,30),G3:G500) -
SUMIF(A3:C500,">"&DATE(2003,7,31),G3:G500)

This extracts totals [in G3:G500] between the above date range. That's
fine, but I need to add another criteria to this. In column 'B', I use
a 'code' to determine what type of stock has been ordered/purchased.
The 'codes' are defined in another sheet (within the same workbook),
so I need the above formula modified to include reference to sheet
'Codes!B4. This way I can break down my different stock per date
range.

All help is appreciated.
Thanks, Cov.
Click to expand...

I suggest you change to SUMPRODUCT. In general, this is of the form
=SUMPRODUCT( (criterion1) * (criterion2) * (criterion3) * data)
You can have one or more criteria.

Your existing formula can be re-written thus:
=SUMPRODUCT((A3:A500>DATE(2003,6,30))*(A3:A500<=DATE(2003,7,31))*G3:G500)
or
=SUMPRODUCT((MONTH(A3:A500)=7)*(YEAR(A3:A500)=2003)*G3:G500)

So, adding your new criterion, we get:
=SUMPRODUCT((A3:A500>DATE(2003,6,30))*(A3:A500<=DATE(2003,7,31))*(B3:B500=Co
des!B4)*G3:G500)
or this:
=SUMPRODUCT((MONTH(A3:A500)=7)*(YEAR(A3:A500)=2003)*(B3:B500=Codes!B4)*G3:G5
00)
Click to expand...

-------------
Thanks to all for your help & input. Further to "Anon's" post, I
removed the 'Codes' sheet reference and ended up with:

=SUMPRODUCT((MONTH(A3:A500)=7)*(YEAR(A3:A500)=2003)*(D3:D500="rm")*G3:G500)

...which worked fine. Great. OK, one more question...

The above gave me the cost of 'rm' over July. How can I get the COUNT
of 'rm' in July? Would I use the COUNT/COUNTIF with the same date
ratio?
Click to expand...
 
Arvi Laanemets said:
Hi

Remove the last array (G3:G500) from your formula, or replace it with 1

Arvi Laanemets


louncov said:
"Anon" <none> wrote in message
G'day,

Am using the following formula to summarise costs between 2 dates:

=SUMIF(A3:A500,">"&DATE(2003,6,30),G3:G500) -
SUMIF(A3:C500,">"&DATE(2003,7,31),G3:G500)

This extracts totals [in G3:G500] between the above date range. That's
fine, but I need to add another criteria to this. In column 'B', I use
a 'code' to determine what type of stock has been ordered/purchased.
The 'codes' are defined in another sheet (within the same workbook),
so I need the above formula modified to include reference to sheet
'Codes!B4. This way I can break down my different stock per date
range.

All help is appreciated.
Thanks, Cov.

I suggest you change to SUMPRODUCT. In general, this is of the form
=SUMPRODUCT( (criterion1) * (criterion2) * (criterion3) * data)
You can have one or more criteria.

Your existing formula can be re-written thus:
=SUMPRODUCT((A3:A500>DATE(2003,6,30))*(A3:A500<=DATE(2003,7,31))*G3:G500)
or
=SUMPRODUCT((MONTH(A3:A500)=7)*(YEAR(A3:A500)=2003)*G3:G500)

So, adding your new criterion, we get:
=SUMPRODUCT((A3:A500>DATE(2003,6,30))*(A3:A500<=DATE(2003,7,31))*(B3:B500=Co
des!B4)*G3:G500)
or this:
=SUMPRODUCT((MONTH(A3:A500)=7)*(YEAR(A3:A500)=2003)*(B3:B500=Codes!B4)*G3:G5
00)
Click to expand...

-------------
Thanks to all for your help & input. Further to "Anon's" post, I
removed the 'Codes' sheet reference and ended up with:

=SUMPRODUCT((MONTH(A3:A500)=7)*(YEAR(A3:A500)=2003)*(D3:D500="rm")*G3:G500)

...which worked fine. Great. OK, one more question...

The above gave me the cost of 'rm' over July. How can I get the COUNT
of 'rm' in July? Would I use the COUNT/COUNTIF with the same date
ratio?
Click to expand...
Click to expand...

*************************
Excellent! One more question to make it 3/3...

I'm using the IF formula to caculate GST (tax) paid on items. I'm
using column D in sheets 'Codes' for non-GST items. Basically, if the
'code' equals 'rm' or 'nat', then GST is 0. In the main sheet, I've
previously hardcoded the formula for calculating GST as...

[Column D in the current sheet is the product code & column G is the
total cost in $]

=IF(OR('Codes'!D4="rm",'Codes'!D4="nat"),SUM(0),SUM(G4/11))

or instead of hardcoding...

=IF(OR(D3=Codes!D2,D3=Codes!D3),SUM(0),SUM(G3/11))

It works, but if I add another item in the 'Codes' sheet, I have to
rewrite the formulas again, which obviously, is not a good idea. I
tried creating a formula that would consider all items in 'Codes'
column D as non-GST...

=IF(D3='Codes'!D2:D100),SUM(0),SUM(G3/11))

This unfortunately, doesn't work. The syntax is correct, but the
result isn't. It only picks up one item (generally D2) from Column D
in 'Codes'.

Question 3a: What is the correct calculation?
Question 3b: Is there a way to check the whole column (D) in 'Codes'
as apposed to a range of D2:D100? I'm not looking at expanding that
much (only have 10 non-GST items at the moment), but I certainly don't
want to revisit this problem in the future!

Again, thanks to all.
 
Arvi Laanemets said:
louncov said:
"Arvi Laanemets" <[email protected]> wrote in message
Hi

Remove the last array (G3:G500) from your formula, or replace it with 1

Arvi Laanemets


"Anon" <none> wrote in message
Click to expand...
G'day,

Am using the following formula to summarise costs between 2 dates:

=SUMIF(A3:A500,">"&DATE(2003,6,30),G3:G500) -
SUMIF(A3:C500,">"&DATE(2003,7,31),G3:G500)

This extracts totals [in G3:G500] between the above date range. That's
fine, but I need to add another criteria to this. In column 'B', I use
a 'code' to determine what type of stock has been ordered/purchased.
The 'codes' are defined in another sheet (within the same workbook),
so I need the above formula modified to include reference to sheet
'Codes!B4. This way I can break down my different stock per date
range.

All help is appreciated.
Thanks, Cov.

I suggest you change to SUMPRODUCT. In general, this is of the form
=SUMPRODUCT( (criterion1) * (criterion2) * (criterion3) * data)
You can have one or more criteria.

Your existing formula can be re-written thus:

=SUMPRODUCT((A3:A500>DATE(2003,6,30))*(A3:A500<=DATE(2003,7,31))*G3:G500)
or
=SUMPRODUCT((MONTH(A3:A500)=7)*(YEAR(A3:A500)=2003)*G3:G500)

So, adding your new criterion, we get:

=SUMPRODUCT((A3:A500>DATE(2003,6,30))*(A3:A500<=DATE(2003,7,31))*(B3:B500=Co
des!B4)*G3:G500)
or this:

=SUMPRODUCT((MONTH(A3:A500)=7)*(YEAR(A3:A500)=2003)*(B3:B500=Codes!B4)*G3:G5
00)

-------------
Thanks to all for your help & input. Further to "Anon's" post, I
removed the 'Codes' sheet reference and ended up with:


=SUMPRODUCT((MONTH(A3:A500)=7)*(YEAR(A3:A500)=2003)*(D3:D500="rm")*G3:G500)

...which worked fine. Great. OK, one more question...

The above gave me the cost of 'rm' over July. How can I get the COUNT
of 'rm' in July? Would I use the COUNT/COUNTIF with the same date
ratio?
Click to expand...

*************************
Excellent! One more question to make it 3/3...

I'm using the IF formula to caculate GST (tax) paid on items. I'm
using column D in sheets 'Codes' for non-GST items. Basically, if the
'code' equals 'rm' or 'nat', then GST is 0. In the main sheet, I've
previously hardcoded the formula for calculating GST as...

[Column D in the current sheet is the product code & column G is the
total cost in $]

=IF(OR('Codes'!D4="rm",'Codes'!D4="nat"),SUM(0),SUM(G4/11))

or instead of hardcoding...

=IF(OR(D3=Codes!D2,D3=Codes!D3),SUM(0),SUM(G3/11))

It works, but if I add another item in the 'Codes' sheet, I have to
rewrite the formulas again, which obviously, is not a good idea. I
tried creating a formula that would consider all items in 'Codes'
column D as non-GST...

=IF(D3='Codes'!D2:D100),SUM(0),SUM(G3/11))

This unfortunately, doesn't work. The syntax is correct, but the
result isn't. It only picks up one item (generally D2) from Column D
in 'Codes'.

Question 3a: What is the correct calculation?
Question 3b: Is there a way to check the whole column (D) in 'Codes'
as apposed to a range of D2:D100? I'm not looking at expanding that
much (only have 10 non-GST items at the moment), but I certainly don't
want to revisit this problem in the future!

Again, thanks to all.
Click to expand...


Hi

=IF(COUNTIF(Codes!D:D,"<>")>0,...)
or, when you have an header row on Codes sheet:
=IF(COUNTIF(Codes!D:D,"<>")>1,...)


Arvi Laanemets
Click to expand...

***********

Arvi,

Thanks again for your prompt response. Unfortunately, now my GST value
is always '0'! The formula now is:
=IF(COUNTIF(Codes!D:D,"<>")>1,SUM(0),G3/11)

I am unsure how this calculation works, but if I type any code not in
the 'Codes!D:D' column [ie., 'Codes'!B:B [GST items]], the GST value
is still '0'. Am I missing something with this calculation? Should the
value I input into the 'code' column of the calculation sheet [Column
D below] be referenced in the above formula as a means of comparison
with 'Codes!D:D'?

[This is also a little confusing with both key columns in both sheets
being 'D']

main calculation sheet
-----------------
D E F G H
code Stock type Quantity CR Amount GST

nat n/a none 100.00 0
slse n/a none 100.00 9.09

Codes sheet
-----------
....B D

....GST items code Non-GST items code
....STCK NAT
....SLSE RM
....LECT AST
CBR
etc...

Thanks again for your time. Now, is there anything I can help you
with?!
 
Hi


louncov said:
"Arvi Laanemets" <[email protected]> wrote in message
louncov said:
"Arvi Laanemets" <[email protected]> wrote in message
Click to expand...
Hi

Remove the last array (G3:G500) from your formula, or replace it with 1

Arvi Laanemets


"Anon" <none> wrote in message
Click to expand...
G'day,

Am using the following formula to summarise costs between 2 dates:

=SUMIF(A3:A500,">"&DATE(2003,6,30),G3:G500) -
SUMIF(A3:C500,">"&DATE(2003,7,31),G3:G500)

This extracts totals [in G3:G500] between the above date
Click to expand...
range.
That's
fine, but I need to add another criteria to this. In column
Click to expand...
'B', I
use
a 'code' to determine what type of stock has been ordered/purchased.
The 'codes' are defined in another sheet (within the same workbook),
so I need the above formula modified to include reference to sheet
'Codes!B4. This way I can break down my different stock per date
range.

All help is appreciated.
Thanks, Cov.

I suggest you change to SUMPRODUCT. In general, this is of the form
=SUMPRODUCT( (criterion1) * (criterion2) * (criterion3) * data)
You can have one or more criteria.

Your existing formula can be re-written thus:
Click to expand...
=SUMPRODUCT((A3:A500>DATE(2003,6,30))*(A3:A500<=DATE(2003,7,31))*G3:G500)
or
=SUMPRODUCT((MONTH(A3:A500)=7)*(YEAR(A3:A500)=2003)*G3:G500)

So, adding your new criterion, we get:
Click to expand...
=SUMPRODUCT((A3:A500>DATE(2003,6,30))*(A3:A500<=DATE(2003,7,31))*(B3:B500=Co
des!B4)*G3:G500)
or this:
Click to expand...
=SUMPRODUCT((MONTH(A3:A500)=7)*(YEAR(A3:A500)=2003)*(B3:B500=Codes!B4)*G3:G5
00)
Click to expand...
=SUMPRODUCT((MONTH(A3:A500)=7)*(YEAR(A3:A500)=2003)*(D3:D500="rm")*G3:G500)
...which worked fine. Great. OK, one more question...

The above gave me the cost of 'rm' over July. How can I get the COUNT
of 'rm' in July? Would I use the COUNT/COUNTIF with the same date
ratio?

*************************
Excellent! One more question to make it 3/3...

I'm using the IF formula to caculate GST (tax) paid on items. I'm
using column D in sheets 'Codes' for non-GST items. Basically, if the
'code' equals 'rm' or 'nat', then GST is 0. In the main sheet, I've
previously hardcoded the formula for calculating GST as...

[Column D in the current sheet is the product code & column G is the
total cost in $]

=IF(OR('Codes'!D4="rm",'Codes'!D4="nat"),SUM(0),SUM(G4/11))

or instead of hardcoding...

=IF(OR(D3=Codes!D2,D3=Codes!D3),SUM(0),SUM(G3/11))

It works, but if I add another item in the 'Codes' sheet, I have to
rewrite the formulas again, which obviously, is not a good idea. I
tried creating a formula that would consider all items in 'Codes'
column D as non-GST...

=IF(D3='Codes'!D2:D100),SUM(0),SUM(G3/11))

This unfortunately, doesn't work. The syntax is correct, but the
result isn't. It only picks up one item (generally D2) from Column D
in 'Codes'.

Question 3a: What is the correct calculation?
Question 3b: Is there a way to check the whole column (D) in 'Codes'
as apposed to a range of D2:D100? I'm not looking at expanding that
much (only have 10 non-GST items at the moment), but I certainly don't
want to revisit this problem in the future!

Again, thanks to all.
Click to expand...


Hi

=IF(COUNTIF(Codes!D:D,"<>")>0,...)
or, when you have an header row on Codes sheet:
=IF(COUNTIF(Codes!D:D,"<>")>1,...)


Arvi Laanemets
Click to expand...

***********

Arvi,

Thanks again for your prompt response. Unfortunately, now my GST value
is always '0'! The formula now is:
=IF(COUNTIF(Codes!D:D,"<>")>1,SUM(0),G3/11)

I am unsure how this calculation works, but if I type any code not in
the 'Codes!D:D' column [ie., 'Codes'!B:B [GST items]], the GST value
is still '0'. Am I missing something with this calculation? Should the
value I input into the 'code' column of the calculation sheet [Column
D below] be referenced in the above formula as a means of comparison
with 'Codes!D:D'?

[This is also a little confusing with both key columns in both sheets
being 'D']

main calculation sheet
-----------------
D E F G H
code Stock type Quantity CR Amount GST

nat n/a none 100.00 0
slse n/a none 100.00 9.09

Codes sheet
-----------
...B D

...GST items code Non-GST items code
...STCK NAT
...SLSE RM
...LECT AST
CBR
etc...

Thanks again for your time. Now, is there anything I can help you
with?!
Click to expand...


Now I understod what you want to do!

Redesign codes sheet:
.... Code GST
.... STCK TRUE
.... SLSE TRUE
.... LECT TRUE
.... NAT FALSE
.... RM FALSE
.... AST FALSE
.... ... ...

Create a named range (Insert.Name.Define):
Codes=OFFSET(Codes!$B$2,,,COUNTIF(Codes!$B:$B,"<>")-1,2)
(so are formulas easier, the named range keeps lookup range in line)

On main sheet, into H2 enter formula:
=G2/11*VLOOKUP(D2,Codes,2,FALSE)
and copy it down as much as you do need.


Arvi Laanemets
 
Back
Top