Excel: defining COUNTIF range based on values in a third column

Question

I'm trying to sort out dyadic data using Excel. I have information on wars, sort of like this:

       Conflict      | Country | Government | Side | repdyads | mondyads | 
Spanish-American War |  Spain  |  monarchy  |  1   |
Spanish-American War |  USA    |  republic  |  2   |
World War II         | Britain |  monarchy  |  1   |
World War II         |  France |  republic  |  1   |
World War II         |  Poland |  republic  |  1   |
World War II         | Germany |  fascist   |  2   |
World War II         |  Italy  |  monarchy  |  2   |

What I'd like to do is create a COUNTIF statement which tells me how often countries of the same 'type' were on opposite sides of a war - so for the above example, return 0 in all columns for the Spanish-American war, because Spain and the US were different types, but return 1 in the monarchy column for World War II because of Britain and Italy (obviously, my real data looks a bit different). I've tried something like this:

=IF(COUNTIF(C3:C8,"monarchy")>0,1)

My problem is that because the wars have different numbers of participants, I can't figure out how to make a single formula which will work for everything. Is it possible to use a VLOOKUP or INDEX formula to specify the range as being everywhere where the A column is consistent with the first line?

Thank you all! Learning excel has been a pain but a lot of fun.

EDIT: expanded above example. Ideal result would be something like this:

       Conflict      | Country | Government | Side | repdyads | mondyads | repmondy |
Spanish-American War |  Spain  |  monarchy  |  1   |    0     |     0    |     1    |
Spanish-American War |  USA    |  republic  |  2   |          |          |          |
World War I          | Britain |  monarchy  |  1   |    0     |     1    |     1    |
World War I          |  France |  republic  |  1   |          |          |          |
World War I          |  Russia |  monarchy  |  1   |          |          |          |
World War I          | Germany |  monarchy  |  2   |          |          |          |
World War I          |  Austria|  monarchy  |  2   |          |          |          |
World War I          | Bulgaria|  monarchy  |  2   |          |          |          |
World War II         | Britain |  monarchy  |  1   |    1     |     0    |     1    |
World War II         |  France |  republic  |  1   |          |          |          |
World War II         |  Poland |  republic  |  1   |          |          |          |
World War II         | Germany |  fascist   |  2   |          |          |          |
World War II         |  Italy  |  monarchy  |  2   |          |          |          |

So this tells me that in the Spanish-American war, republics fought monarchies. In WWI, Monarchies fought monarchies and republics fought monarchies, but republics did not fight other republics. The number of dyads is not important, rather who is fighting who.

Answer 1

Assuming there is only 2 sides of a war.. then the

how often countries of the same 'type' were on opposite sides of a war

question can be answered properly...

The key is.. if "Conflict"-"Government"-"Side" is "World War I"-"monarchy"-"1" , then we just need to look for "World War I"-"monarchy"-"2" . If found, then mark 'Yes', else mark null.

Assuming the "Conflict" column header is located at A1.

1. Put "same-govt-fights?" text in H1,
2. then in H2 cell put these formula, and drag downwards.

=IFERROR(IF(INDEX($C$2:$C$14,MATCH(1,INDEX((A2=$A$2:$A$14)*(C2=$C$2:$C$14)*(IF(D2=1,2,1)=$D$2:$D$14),0,1),0))=C2,"Yes",""),"")

3. Done.

Hope it helps.

p/s : info for multiple match index()+match() = link.