Having problems with SUMIFS and trying to get an extra condition in

Hi,

Four people are having a party, they are inviting a number of people who are the same, in some cases they all know the same people. If this is the case then YES will be in each "named column" ie Amanda, Craig, Jason & James and the value 4 will be in % Value column. The same is true for only three YES, two YES and One YES.

Using the following formula I am able to workout what the total number of people invited is by dividing guests up ie if only one person has invited somebody(s) (maybe a couple) then there is only one YES in a "named column" but those people are counted in.

[=SUMIFS(G12:G538,K12:K538,"1")+(SUMIFS(G12:G538,K12:K538,"2")/2)+(SUMIFS(G12:G538,K12:K538,"3")/3)+(SUMIFS(G12:G538,K12:K538,"4")/4)

What I'm trying to workout is how many proportional people each person has invited ie if all 4 "named column's" are a YES and that invite was to two people - then each person has 0.5 of a person as part of their budget.
So I need an enclosing type of SUMIFS that would be able to ie. check that for "JUST" the James column with a YES in it do the above formula and return the total number of people James has only invited or invited with one or more of the other party holders. I intend to do this for each "named column" and for each category of Invited, Unsure, Accepted.

Believe me the party should be great :-/

Many thanks for any help

James...

I read this about 10 hours ago and it made no sense to me at all. I've just looked at it again and I still don't quite understand what you want. I've made a bit of a mad guess. see blue shaded cells. It would be rather more help if you catually shiwed us you (manually calculated) results, explaining how you got them. Giving us a formula that doesn't work is of limited value....

So far as I can see there are a total of 10 people invited and you want some sort of way of assigning proportionally how many of that 10 were invited by each of your four people.

To be honest, I also think it would be more help conceptualising this if we knew what data you are actually working with. A party where 0.3 of a person is invited does sound a bit grizzly!!

Dear Glenn,

Many thanks for taking the time to try and understand what I was trying to achieve.
I think you are partly there. What I was hoping for is something like this:

If there is a YES in the James column and blanks in Amanda, Jason & Craig then the number of guests in the Invited column is added to James's total of Invited guests
If there is a YES in the James column and ONE other party organiser then the number of guests in the invited columns (for James and other party organiser) is divided by 2 between James and that ONE other party organiser ie 2 guests divided by 2 = 1 added to each of those two totals ie James & Amanda
If there is a YES in the James column and TWO other party organisers then the number of guests in the invited column is divided by 3 between James and those TWO other party organiser ie 2 guests divided by 3 = 0.67 added to each of those three totals
If there is a YES in the all columns (James, Amanda, Jason & Craig) then the number of guests in the invited column is divided by 4 and equally between all the party organiser ie 2 guests divided by 4 = 0.5 added to each of their totals

I will try and test your solution - but maybe you will know already whether it will achieve these results
Many thanks again for your interest in this odd problem
James...

I have had a further look and am not sure I entirely understand your approach (not that I know anything :-). But one thing I did want to achieve is finding out once everybody has submitted there guest lists is:
How many proportional people Amanda, James, Craig & Jason have invited each.
How many proportional people Amanda, James, Craig & Jason are still unsure (each) (at any one time).
How many proportional people Amanda, James, Craig & Jason have accepted (each) (ie are coming).

Hi,

This is also something of a shot in the dark but perhaps
=SUMPRODUCT(--($F$12:$F$17="Yes"),$G$12:$G$17,1/$K$12:$K$17)
for James.

Edited: having now looked at Glenn's workbook, it appears I have made the same guess albeit using only the existing columns you had.