Question description:
This user has given permission to use the problem statement for this
blog.
Adding criteria to If(And( ),Sumproducts()
Solved by G. U. in 19 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
10/08/2017 - 12:50
Hi, could you provide a data example, please
Excelchat Expert
10/08/2017 - 12:51
Are you there?
User
10/08/2017 - 12:52
hi
User
10/08/2017 - 12:52
yes
User
10/08/2017 - 12:52
i have the following formula
User
10/08/2017 - 12:53
I just need this to be able to look at multiple sections for the criteria
Excelchat Expert
10/08/2017 - 12:54
I didn't get what exactly you want me to explain you?
User
10/08/2017 - 12:54
i.e if c22 is daily and g22 is B7, but also add others with the same criteria
User
10/08/2017 - 12:54
so currently the forumla is in e3:ex
Excelchat Expert
10/08/2017 - 12:55
do you have skype?
User
10/08/2017 - 12:55
I want it to be able to see that multiple ones have criteria
User
10/08/2017 - 12:55
no sorry
Excelchat Expert
10/08/2017 - 12:55
okay, leave me your email then
Excelchat Expert
10/08/2017 - 12:55
Looks like it is not gonna be enough of this time
User
10/08/2017 - 12:55
can you point me in the right direction then?
Excelchat Expert
10/08/2017 - 12:57
I just still don't get what you want this formula to show you. That's why I need more data for the example, that i could see references of this formula
Excelchat Expert
10/08/2017 - 12:57
Then I will think it over and send you back a result via email
Excelchat Expert
10/08/2017 - 12:57
but i still don;t have any ofyour contact
User
10/08/2017 - 12:58
At the moment it is only calculating that the top result on the right is fitting the criteria of the formula
User
10/08/2017 - 12:58
i.e C22 and G22 in the formula
User
10/08/2017 - 12:58
but i need this to be able to look across the whole table 2
Excelchat Expert
10/08/2017 - 12:59
look, The C22 and G22 are BLANK. There is no Table2. I can't fix your problem without the data. You may send another data if it is classified, just change them for an example
Excelchat Expert
10/08/2017 - 12:59
it is really difficult to understand even without a real spreadsheet
Excelchat Expert
10/08/2017 - 01:00
ny enail: tieliegin@gmail.com in case the session is over, please write it down
Excelchat Expert
10/08/2017 - 01:00
*my
User
10/08/2017 - 01:00
I have put the data in the screen on the right?
Excelchat Expert
10/08/2017 - 01:00
yes, but it's not enough
User
10/08/2017 - 01:01
C22 and g22 is just the formula i am using but there is too much to send or look at
Excelchat Expert
10/08/2017 - 01:01
and the error in your formula shows me ERROR of the reference
User
10/08/2017 - 01:01
=IF(AND(Savings!C22="Daily",Savings!G22=Savings!$B$7),SUMPRODUCT(--(C6>=Savings!$E$22:$E$31)*(C6<=Savings!$F$22:$F$31),Savings!$D$22:$D$31)*365,0)
User
10/08/2017 - 01:01
all i want to know is how to add multiple fields to fit the criteria
User
10/08/2017 - 01:01
I.e more than just c22 and G22
Excelchat Expert
10/08/2017 - 01:02
but you already have used AND() function here, basically you can use it anywhere
User
10/08/2017 - 01:02
so i cant make it so it is looking at the whole table? it will only ever be able to look at c22 and g22>?
User
10/08/2017 - 01:03
as i need the first line of the formula to look at say C22:C30 and G22:30
User
10/08/2017 - 01:03
and all the ones that fit the criteria within those ranged
User
10/08/2017 - 01:03
ranges*
Excelchat Expert
10/08/2017 - 01:03
so it is not about specifying different criteria, it is about, perhaps formula of array
User
10/08/2017 - 01:03
yes
Excelchat Expert
10/08/2017 - 01:03
do you know how it works?
Excelchat Expert
10/08/2017 - 01:04
in general?
User
10/08/2017 - 01:04
the criteria itself is fine, its just its only looking at the specified cells
User
10/08/2017 - 01:04
so in my data there, it is looking at G4 and K4 and putting in one figure
User
10/08/2017 - 01:04
but i need it to look across it all
User
10/08/2017 - 01:05
ie any that fit the Daily and ISA criteria from G2:G5 and K2:K%
User
10/08/2017 - 01:05
K5*
Excelchat Expert
10/08/2017 - 01:05
try to press Ctrl+Shift+Enter to apply array
Excelchat Expert
10/08/2017 - 01:06
I got you i think, but basically I need to see this result in order to modify your formula.
User
10/08/2017 - 01:06
with the same formula?
Excelchat Expert
10/08/2017 - 01:07
i'm not sure, but try to specify range in the formula and apply Ctrl+Shift+Enter
Excelchat Expert
10/08/2017 - 01:07
I can not say you for sure just because I do not see the whole example data, sorry
Excelchat Expert
10/08/2017 - 01:09
The way we work here is that we are provided with the example data and show how the things work. Otherwise it does not work good
User
10/08/2017 - 01:09
okay
Excelchat Expert
10/08/2017 - 01:09
You may email me full info and I will help you, just don't want to to mislead you in a rush
User
10/08/2017 - 01:09
ill try the next person and try give more
User
10/08/2017 - 01:09
ok
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
