Sumproduct

P

Peter100

Well-known Member
Joined
Apr 21, 2002
Messages
765
I have 2 columns of data
column A contains dates in the format mmm/yy
column B contains monetary amounts ie £300.67

I want to sum the figures in column B if the entry in column A is equal to or between two values in F! and G1

How do I get sumproduct to do this ? :(
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Sort by date Sort by votes
You can use SUMPRODUCT but SUMIF would also do the job

=SUMIF(A:A,">="&F1,B:B)-SUMIF(A:A,">"&G1,B:B)

the alternative is

=SUMPRODUCT(--(A1:A100>=F1),--(A1:A100<=G1),B1:B100)
 
Upvote 0
My error in the listing, I also have a third criteria that it must also equal a text value in column C
 
Upvote 0
From your other locked post


Code: 
=SUMPRODUCT(--(A2:A26>=F1),--(A2:A26<=G1),--(B2:B26="purchases"),C2:C26)
 
Upvote 0
Peter this should work, just add another if for your column C

=+IF(B2="purchase",IF((AND(F1<A2,A2<G1)),SUMIF(B2:C7,B2,C2:C8)),"")

I have Column A date, Column B "purchase" and column C amount. You can add up to 7 arguements
 
Upvote 0
'=+IF(B2="purchase",IF((AND(F1<A2,A2<G1)),SUMIF(B2:C7,B2,C2:C8)),"")

Why didn't the whole formula showed up????
 
Upvote 0
<A2,A2<G1)),SUMIF(B2:C7,B2,C2:C8)),"")
last attempt.

'=+IF(B2="purchase",IF((AND(F1
<A2,A2<G1)),SUMIF(B2:C7,B2,C2:C8)),"")
 
Upvote 0
highndry said:
'=+IF(B2="purchase",IF((AND(F1<A2,A2<G1)),SUMIF(B2:C7,B2,C2:C8)),"")

Why didn't the whole formula showed up????
Click to expand...

Hi highndry

The board doesn't like < and > signs, they can be misinterpreted as HTML tags,

put in spaces like this....

=(A1< B1)*(C1 >B1)

or use the code designation like...

Code: 
=(A1<B1)*(C1>B1)
 
Upvote 0
IF(B2="purchase",IF((AND(F1"greater than symbol"A2,A2"greater than symbol"G1)),SUMIF(B2:C7,B2,C2:C8)),"")
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
Sumif vs Sumproduct
2
Replies
11
Views
194
etaf
etaf
S
  • Question
how to enter a value in a specific cell based on column and row headers
Replies
4
Views
115
Shazzj001
S
CJG19
  • Solved
Title New Column Text plus Today's Date
Replies
3
Views
95
Joe4
Joe4
J
  • Question
Sum data in a range based on a column and row and an equation
Replies
1
Views
141
bobaol
B
R
  • Solved
Adding multiple columns with criteria in one column
Replies
7
Views
155
SanjayGMusafir
SanjayGMusafir

Forum statistics

Threads
1,214,985
Messages
6,122,606
Members
449,089
Latest member
Motoracer88

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top